1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    seq_get,
  30    split_num_words,
  31    subclasses,
  32)
  33from sqlglot.tokens import Token
  34
  35if t.TYPE_CHECKING:
  36    from sqlglot.dialects.dialect import DialectType
  37
  38
  39class _Expression(type):
  40    def __new__(cls, clsname, bases, attrs):
  41        klass = super().__new__(cls, clsname, bases, attrs)
  42
  43        # When an Expression class is created, its key is automatically set to be
  44        # the lowercase version of the class' name.
  45        klass.key = clsname.lower()
  46
  47        # This is so that docstrings are not inherited in pdoc
  48        klass.__doc__ = klass.__doc__ or ""
  49
  50        return klass
  51
  52
  53class Expression(metaclass=_Expression):
  54    """
  55    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  56    context, such as its child expressions, their names (arg keys), and whether a given child expression
  57    is optional or not.
  58
  59    Attributes:
  60        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  61            and representing expressions as strings.
  62        arg_types: determines what arguments (child nodes) are supported by an expression. It
  63            maps arg keys to booleans that indicate whether the corresponding args are optional.
  64
  65    Example:
  66        >>> class Foo(Expression):
  67        ...     arg_types = {"this": True, "expression": False}
  68
  69        The above definition informs us that Foo is an Expression that requires an argument called
  70        "this" and may also optionally receive an argument called "expression".
  71
  72    Args:
  73        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  74        parent: a reference to the parent expression (or None, in case of root expressions).
  75        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  76            uses to refer to it.
  77        comments: a list of comments that are associated with a given expression. This is used in
  78            order to preserve comments when transpiling SQL code.
  79        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  80            optimizer, in order to enable some transformations that require type information.
  81    """
  82
  83    key = "expression"
  84    arg_types = {"this": True}
  85    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta")
  86
  87    def __init__(self, **args: t.Any):
  88        self.args: t.Dict[str, t.Any] = args
  89        self.parent: t.Optional[Expression] = None
  90        self.arg_key: t.Optional[str] = None
  91        self.comments: t.Optional[t.List[str]] = None
  92        self._type: t.Optional[DataType] = None
  93        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  94
  95        for arg_key, value in self.args.items():
  96            self._set_parent(arg_key, value)
  97
  98    def __eq__(self, other) -> bool:
  99        return type(self) is type(other) and _norm_args(self) == _norm_args(other)
 100
 101    def __hash__(self) -> int:
 102        return hash(
 103            (
 104                self.key,
 105                tuple(
 106                    (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items()
 107                ),
 108            )
 109        )
 110
 111    @property
 112    def this(self):
 113        """
 114        Retrieves the argument with key "this".
 115        """
 116        return self.args.get("this")
 117
 118    @property
 119    def expression(self):
 120        """
 121        Retrieves the argument with key "expression".
 122        """
 123        return self.args.get("expression")
 124
 125    @property
 126    def expressions(self):
 127        """
 128        Retrieves the argument with key "expressions".
 129        """
 130        return self.args.get("expressions") or []
 131
 132    def text(self, key) -> str:
 133        """
 134        Returns a textual representation of the argument corresponding to "key". This can only be used
 135        for args that are strings or leaf Expression instances, such as identifiers and literals.
 136        """
 137        field = self.args.get(key)
 138        if isinstance(field, str):
 139            return field
 140        if isinstance(field, (Identifier, Literal, Var)):
 141            return field.this
 142        if isinstance(field, (Star, Null)):
 143            return field.name
 144        return ""
 145
 146    @property
 147    def is_string(self) -> bool:
 148        """
 149        Checks whether a Literal expression is a string.
 150        """
 151        return isinstance(self, Literal) and self.args["is_string"]
 152
 153    @property
 154    def is_number(self) -> bool:
 155        """
 156        Checks whether a Literal expression is a number.
 157        """
 158        return isinstance(self, Literal) and not self.args["is_string"]
 159
 160    @property
 161    def is_int(self) -> bool:
 162        """
 163        Checks whether a Literal expression is an integer.
 164        """
 165        if self.is_number:
 166            try:
 167                int(self.name)
 168                return True
 169            except ValueError:
 170                pass
 171        return False
 172
 173    @property
 174    def is_star(self) -> bool:
 175        """Checks whether an expression is a star."""
 176        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 177
 178    @property
 179    def alias(self) -> str:
 180        """
 181        Returns the alias of the expression, or an empty string if it's not aliased.
 182        """
 183        if isinstance(self.args.get("alias"), TableAlias):
 184            return self.args["alias"].name
 185        return self.text("alias")
 186
 187    @property
 188    def name(self) -> str:
 189        return self.text("this")
 190
 191    @property
 192    def alias_or_name(self):
 193        return self.alias or self.name
 194
 195    @property
 196    def output_name(self):
 197        """
 198        Name of the output column if this expression is a selection.
 199
 200        If the Expression has no output name, an empty string is returned.
 201
 202        Example:
 203            >>> from sqlglot import parse_one
 204            >>> parse_one("SELECT a").expressions[0].output_name
 205            'a'
 206            >>> parse_one("SELECT b AS c").expressions[0].output_name
 207            'c'
 208            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 209            ''
 210        """
 211        return ""
 212
 213    @property
 214    def type(self) -> t.Optional[DataType]:
 215        return self._type
 216
 217    @type.setter
 218    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 219        if dtype and not isinstance(dtype, DataType):
 220            dtype = DataType.build(dtype)
 221        self._type = dtype  # type: ignore
 222
 223    @property
 224    def meta(self) -> t.Dict[str, t.Any]:
 225        if self._meta is None:
 226            self._meta = {}
 227        return self._meta
 228
 229    def __deepcopy__(self, memo):
 230        copy = self.__class__(**deepcopy(self.args))
 231        if self.comments is not None:
 232            copy.comments = deepcopy(self.comments)
 233
 234        if self._type is not None:
 235            copy._type = self._type.copy()
 236
 237        if self._meta is not None:
 238            copy._meta = deepcopy(self._meta)
 239
 240        return copy
 241
 242    def copy(self):
 243        """
 244        Returns a deep copy of the expression.
 245        """
 246        new = deepcopy(self)
 247        new.parent = self.parent
 248        for item, parent, _ in new.bfs():
 249            if isinstance(item, Expression) and parent:
 250                item.parent = parent
 251        return new
 252
 253    def append(self, arg_key, value):
 254        """
 255        Appends value to arg_key if it's a list or sets it as a new list.
 256
 257        Args:
 258            arg_key (str): name of the list expression arg
 259            value (Any): value to append to the list
 260        """
 261        if not isinstance(self.args.get(arg_key), list):
 262            self.args[arg_key] = []
 263        self.args[arg_key].append(value)
 264        self._set_parent(arg_key, value)
 265
 266    def set(self, arg_key, value):
 267        """
 268        Sets `arg_key` to `value`.
 269
 270        Args:
 271            arg_key (str): name of the expression arg.
 272            value: value to set the arg to.
 273        """
 274        self.args[arg_key] = value
 275        self._set_parent(arg_key, value)
 276
 277    def _set_parent(self, arg_key, value):
 278        if isinstance(value, Expression):
 279            value.parent = self
 280            value.arg_key = arg_key
 281        elif isinstance(value, list):
 282            for v in value:
 283                if isinstance(v, Expression):
 284                    v.parent = self
 285                    v.arg_key = arg_key
 286
 287    @property
 288    def depth(self):
 289        """
 290        Returns the depth of this tree.
 291        """
 292        if self.parent:
 293            return self.parent.depth + 1
 294        return 0
 295
 296    def find(self, *expression_types, bfs=True):
 297        """
 298        Returns the first node in this tree which matches at least one of
 299        the specified types.
 300
 301        Args:
 302            expression_types (type): the expression type(s) to match.
 303
 304        Returns:
 305            The node which matches the criteria or None if no such node was found.
 306        """
 307        return next(self.find_all(*expression_types, bfs=bfs), None)
 308
 309    def find_all(self, *expression_types, bfs=True):
 310        """
 311        Returns a generator object which visits all nodes in this tree and only
 312        yields those that match at least one of the specified expression types.
 313
 314        Args:
 315            expression_types (type): the expression type(s) to match.
 316
 317        Returns:
 318            The generator object.
 319        """
 320        for expression, _, _ in self.walk(bfs=bfs):
 321            if isinstance(expression, expression_types):
 322                yield expression
 323
 324    def find_ancestor(self, *expression_types):
 325        """
 326        Returns a nearest parent matching expression_types.
 327
 328        Args:
 329            expression_types (type): the expression type(s) to match.
 330
 331        Returns:
 332            The parent node.
 333        """
 334        ancestor = self.parent
 335        while ancestor and not isinstance(ancestor, expression_types):
 336            ancestor = ancestor.parent
 337        return ancestor
 338
 339    @property
 340    def parent_select(self):
 341        """
 342        Returns the parent select statement.
 343        """
 344        return self.find_ancestor(Select)
 345
 346    def walk(self, bfs=True, prune=None):
 347        """
 348        Returns a generator object which visits all nodes in this tree.
 349
 350        Args:
 351            bfs (bool): if set to True the BFS traversal order will be applied,
 352                otherwise the DFS traversal will be used instead.
 353            prune ((node, parent, arg_key) -> bool): callable that returns True if
 354                the generator should stop traversing this branch of the tree.
 355
 356        Returns:
 357            the generator object.
 358        """
 359        if bfs:
 360            yield from self.bfs(prune=prune)
 361        else:
 362            yield from self.dfs(prune=prune)
 363
 364    def dfs(self, parent=None, key=None, prune=None):
 365        """
 366        Returns a generator object which visits all nodes in this tree in
 367        the DFS (Depth-first) order.
 368
 369        Returns:
 370            The generator object.
 371        """
 372        parent = parent or self.parent
 373        yield self, parent, key
 374        if prune and prune(self, parent, key):
 375            return
 376
 377        for k, v in self.args.items():
 378            for node in ensure_collection(v):
 379                if isinstance(node, Expression):
 380                    yield from node.dfs(self, k, prune)
 381
 382    def bfs(self, prune=None):
 383        """
 384        Returns a generator object which visits all nodes in this tree in
 385        the BFS (Breadth-first) order.
 386
 387        Returns:
 388            The generator object.
 389        """
 390        queue = deque([(self, self.parent, None)])
 391
 392        while queue:
 393            item, parent, key = queue.popleft()
 394
 395            yield item, parent, key
 396            if prune and prune(item, parent, key):
 397                continue
 398
 399            if isinstance(item, Expression):
 400                for k, v in item.args.items():
 401                    for node in ensure_collection(v):
 402                        if isinstance(node, Expression):
 403                            queue.append((node, item, k))
 404
 405    def unnest(self):
 406        """
 407        Returns the first non parenthesis child or self.
 408        """
 409        expression = self
 410        while isinstance(expression, Paren):
 411            expression = expression.this
 412        return expression
 413
 414    def unalias(self):
 415        """
 416        Returns the inner expression if this is an Alias.
 417        """
 418        if isinstance(self, Alias):
 419            return self.this
 420        return self
 421
 422    def unnest_operands(self):
 423        """
 424        Returns unnested operands as a tuple.
 425        """
 426        return tuple(arg.unnest() for arg in self.args.values() if arg)
 427
 428    def flatten(self, unnest=True):
 429        """
 430        Returns a generator which yields child nodes who's parents are the same class.
 431
 432        A AND B AND C -> [A, B, C]
 433        """
 434        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)):
 435            if not isinstance(node, self.__class__):
 436                yield node.unnest() if unnest else node
 437
 438    def __str__(self):
 439        return self.sql()
 440
 441    def __repr__(self):
 442        return self._to_s()
 443
 444    def sql(self, dialect: DialectType = None, **opts) -> str:
 445        """
 446        Returns SQL string representation of this tree.
 447
 448        Args:
 449            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 450            opts: other `sqlglot.generator.Generator` options.
 451
 452        Returns:
 453            The SQL string.
 454        """
 455        from sqlglot.dialects import Dialect
 456
 457        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 458
 459    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 460        indent = "" if not level else "\n"
 461        indent += "".join(["  "] * level)
 462        left = f"({self.key.upper()} "
 463
 464        args: t.Dict[str, t.Any] = {
 465            k: ", ".join(
 466                v._to_s(hide_missing=hide_missing, level=level + 1)
 467                if hasattr(v, "_to_s")
 468                else str(v)
 469                for v in ensure_collection(vs)
 470                if v is not None
 471            )
 472            for k, vs in self.args.items()
 473        }
 474        args["comments"] = self.comments
 475        args["type"] = self.type
 476        args = {k: v for k, v in args.items() if v or not hide_missing}
 477
 478        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 479        right += ")"
 480
 481        return indent + left + right
 482
 483    def transform(self, fun, *args, copy=True, **kwargs):
 484        """
 485        Recursively visits all tree nodes (excluding already transformed ones)
 486        and applies the given transformation function to each node.
 487
 488        Args:
 489            fun (function): a function which takes a node as an argument and returns a
 490                new transformed node or the same node without modifications. If the function
 491                returns None, then the corresponding node will be removed from the syntax tree.
 492            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 493                modified in place.
 494
 495        Returns:
 496            The transformed tree.
 497        """
 498        node = self.copy() if copy else self
 499        new_node = fun(node, *args, **kwargs)
 500
 501        if new_node is None or not isinstance(new_node, Expression):
 502            return new_node
 503        if new_node is not node:
 504            new_node.parent = node.parent
 505            return new_node
 506
 507        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 508        return new_node
 509
 510    def replace(self, expression):
 511        """
 512        Swap out this expression with a new expression.
 513
 514        For example::
 515
 516            >>> tree = Select().select("x").from_("tbl")
 517            >>> tree.find(Column).replace(Column(this="y"))
 518            (COLUMN this: y)
 519            >>> tree.sql()
 520            'SELECT y FROM tbl'
 521
 522        Args:
 523            expression (Expression|None): new node
 524
 525        Returns:
 526            The new expression or expressions.
 527        """
 528        if not self.parent:
 529            return expression
 530
 531        parent = self.parent
 532        self.parent = None
 533
 534        replace_children(parent, lambda child: expression if child is self else child)
 535        return expression
 536
 537    def pop(self):
 538        """
 539        Remove this expression from its AST.
 540        """
 541        self.replace(None)
 542
 543    def assert_is(self, type_):
 544        """
 545        Assert that this `Expression` is an instance of `type_`.
 546
 547        If it is NOT an instance of `type_`, this raises an assertion error.
 548        Otherwise, this returns this expression.
 549
 550        Examples:
 551            This is useful for type security in chained expressions:
 552
 553            >>> import sqlglot
 554            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 555            'SELECT x, z FROM y'
 556        """
 557        assert isinstance(self, type_)
 558        return self
 559
 560    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 561        """
 562        Checks if this expression is valid (e.g. all mandatory args are set).
 563
 564        Args:
 565            args: a sequence of values that were used to instantiate a Func expression. This is used
 566                to check that the provided arguments don't exceed the function argument limit.
 567
 568        Returns:
 569            A list of error messages for all possible errors that were found.
 570        """
 571        errors: t.List[str] = []
 572
 573        for k in self.args:
 574            if k not in self.arg_types:
 575                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 576        for k, mandatory in self.arg_types.items():
 577            v = self.args.get(k)
 578            if mandatory and (v is None or (isinstance(v, list) and not v)):
 579                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 580
 581        if (
 582            args
 583            and isinstance(self, Func)
 584            and len(args) > len(self.arg_types)
 585            and not self.is_var_len_args
 586        ):
 587            errors.append(
 588                f"The number of provided arguments ({len(args)}) is greater than "
 589                f"the maximum number of supported arguments ({len(self.arg_types)})"
 590            )
 591
 592        return errors
 593
 594    def dump(self):
 595        """
 596        Dump this Expression to a JSON-serializable dict.
 597        """
 598        from sqlglot.serde import dump
 599
 600        return dump(self)
 601
 602    @classmethod
 603    def load(cls, obj):
 604        """
 605        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 606        """
 607        from sqlglot.serde import load
 608
 609        return load(obj)
 610
 611
 612IntoType = t.Union[
 613    str,
 614    t.Type[Expression],
 615    t.Collection[t.Union[str, t.Type[Expression]]],
 616]
 617
 618
 619class Condition(Expression):
 620    def and_(self, *expressions, dialect=None, **opts):
 621        """
 622        AND this condition with one or multiple expressions.
 623
 624        Example:
 625            >>> condition("x=1").and_("y=1").sql()
 626            'x = 1 AND y = 1'
 627
 628        Args:
 629            *expressions (str | Expression): the SQL code strings to parse.
 630                If an `Expression` instance is passed, it will be used as-is.
 631            dialect (str): the dialect used to parse the input expression.
 632            opts (kwargs): other options to use to parse the input expressions.
 633
 634        Returns:
 635            And: the new condition.
 636        """
 637        return and_(self, *expressions, dialect=dialect, **opts)
 638
 639    def or_(self, *expressions, dialect=None, **opts):
 640        """
 641        OR this condition with one or multiple expressions.
 642
 643        Example:
 644            >>> condition("x=1").or_("y=1").sql()
 645            'x = 1 OR y = 1'
 646
 647        Args:
 648            *expressions (str | Expression): the SQL code strings to parse.
 649                If an `Expression` instance is passed, it will be used as-is.
 650            dialect (str): the dialect used to parse the input expression.
 651            opts (kwargs): other options to use to parse the input expressions.
 652
 653        Returns:
 654            Or: the new condition.
 655        """
 656        return or_(self, *expressions, dialect=dialect, **opts)
 657
 658    def not_(self):
 659        """
 660        Wrap this condition with NOT.
 661
 662        Example:
 663            >>> condition("x=1").not_().sql()
 664            'NOT x = 1'
 665
 666        Returns:
 667            Not: the new condition.
 668        """
 669        return not_(self)
 670
 671
 672class Predicate(Condition):
 673    """Relationships like x = y, x > 1, x >= y."""
 674
 675
 676class DerivedTable(Expression):
 677    @property
 678    def alias_column_names(self):
 679        table_alias = self.args.get("alias")
 680        if not table_alias:
 681            return []
 682        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 683        return [c.name for c in column_list]
 684
 685    @property
 686    def selects(self):
 687        alias = self.args.get("alias")
 688
 689        if alias:
 690            return alias.columns
 691        return []
 692
 693    @property
 694    def named_selects(self):
 695        return [select.output_name for select in self.selects]
 696
 697
 698class Unionable(Expression):
 699    def union(self, expression, distinct=True, dialect=None, **opts):
 700        """
 701        Builds a UNION expression.
 702
 703        Example:
 704            >>> import sqlglot
 705            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 706            'SELECT * FROM foo UNION SELECT * FROM bla'
 707
 708        Args:
 709            expression (str | Expression): the SQL code string.
 710                If an `Expression` instance is passed, it will be used as-is.
 711            distinct (bool): set the DISTINCT flag if and only if this is true.
 712            dialect (str): the dialect used to parse the input expression.
 713            opts (kwargs): other options to use to parse the input expressions.
 714        Returns:
 715            Union: the Union expression.
 716        """
 717        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 718
 719    def intersect(self, expression, distinct=True, dialect=None, **opts):
 720        """
 721        Builds an INTERSECT expression.
 722
 723        Example:
 724            >>> import sqlglot
 725            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 726            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 727
 728        Args:
 729            expression (str | Expression): the SQL code string.
 730                If an `Expression` instance is passed, it will be used as-is.
 731            distinct (bool): set the DISTINCT flag if and only if this is true.
 732            dialect (str): the dialect used to parse the input expression.
 733            opts (kwargs): other options to use to parse the input expressions.
 734        Returns:
 735            Intersect: the Intersect expression
 736        """
 737        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 738
 739    def except_(self, expression, distinct=True, dialect=None, **opts):
 740        """
 741        Builds an EXCEPT expression.
 742
 743        Example:
 744            >>> import sqlglot
 745            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 746            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 747
 748        Args:
 749            expression (str | Expression): the SQL code string.
 750                If an `Expression` instance is passed, it will be used as-is.
 751            distinct (bool): set the DISTINCT flag if and only if this is true.
 752            dialect (str): the dialect used to parse the input expression.
 753            opts (kwargs): other options to use to parse the input expressions.
 754        Returns:
 755            Except: the Except expression
 756        """
 757        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 758
 759
 760class UDTF(DerivedTable, Unionable):
 761    pass
 762
 763
 764class Cache(Expression):
 765    arg_types = {
 766        "with": False,
 767        "this": True,
 768        "lazy": False,
 769        "options": False,
 770        "expression": False,
 771    }
 772
 773
 774class Uncache(Expression):
 775    arg_types = {"this": True, "exists": False}
 776
 777
 778class Create(Expression):
 779    arg_types = {
 780        "with": False,
 781        "this": True,
 782        "kind": True,
 783        "expression": False,
 784        "set": False,
 785        "multiset": False,
 786        "global_temporary": False,
 787        "volatile": False,
 788        "exists": False,
 789        "properties": False,
 790        "temporary": False,
 791        "transient": False,
 792        "external": False,
 793        "replace": False,
 794        "unique": False,
 795        "materialized": False,
 796        "data": False,
 797        "statistics": False,
 798        "no_primary_index": False,
 799        "indexes": False,
 800        "no_schema_binding": False,
 801        "begin": False,
 802    }
 803
 804
 805class Describe(Expression):
 806    arg_types = {"this": True, "kind": False}
 807
 808
 809class Set(Expression):
 810    arg_types = {"expressions": True}
 811
 812
 813class SetItem(Expression):
 814    arg_types = {
 815        "this": False,
 816        "expressions": False,
 817        "kind": False,
 818        "collate": False,  # MySQL SET NAMES statement
 819        "global": False,
 820    }
 821
 822
 823class Show(Expression):
 824    arg_types = {
 825        "this": True,
 826        "target": False,
 827        "offset": False,
 828        "limit": False,
 829        "like": False,
 830        "where": False,
 831        "db": False,
 832        "full": False,
 833        "mutex": False,
 834        "query": False,
 835        "channel": False,
 836        "global": False,
 837        "log": False,
 838        "position": False,
 839        "types": False,
 840    }
 841
 842
 843class UserDefinedFunction(Expression):
 844    arg_types = {"this": True, "expressions": False, "wrapped": False}
 845
 846
 847class CharacterSet(Expression):
 848    arg_types = {"this": True, "default": False}
 849
 850
 851class With(Expression):
 852    arg_types = {"expressions": True, "recursive": False}
 853
 854    @property
 855    def recursive(self) -> bool:
 856        return bool(self.args.get("recursive"))
 857
 858
 859class WithinGroup(Expression):
 860    arg_types = {"this": True, "expression": False}
 861
 862
 863class CTE(DerivedTable):
 864    arg_types = {"this": True, "alias": True}
 865
 866
 867class TableAlias(Expression):
 868    arg_types = {"this": False, "columns": False}
 869
 870    @property
 871    def columns(self):
 872        return self.args.get("columns") or []
 873
 874
 875class BitString(Condition):
 876    pass
 877
 878
 879class HexString(Condition):
 880    pass
 881
 882
 883class ByteString(Condition):
 884    pass
 885
 886
 887class Column(Condition):
 888    arg_types = {"this": True, "table": False, "db": False, "catalog": False}
 889
 890    @property
 891    def table(self) -> str:
 892        return self.text("table")
 893
 894    @property
 895    def db(self) -> str:
 896        return self.text("db")
 897
 898    @property
 899    def catalog(self) -> str:
 900        return self.text("catalog")
 901
 902    @property
 903    def output_name(self) -> str:
 904        return self.name
 905
 906
 907class ColumnDef(Expression):
 908    arg_types = {
 909        "this": True,
 910        "kind": False,
 911        "constraints": False,
 912        "exists": False,
 913    }
 914
 915
 916class AlterColumn(Expression):
 917    arg_types = {
 918        "this": True,
 919        "dtype": False,
 920        "collate": False,
 921        "using": False,
 922        "default": False,
 923        "drop": False,
 924    }
 925
 926
 927class RenameTable(Expression):
 928    pass
 929
 930
 931class ColumnConstraint(Expression):
 932    arg_types = {"this": False, "kind": True}
 933
 934
 935class ColumnConstraintKind(Expression):
 936    pass
 937
 938
 939class AutoIncrementColumnConstraint(ColumnConstraintKind):
 940    pass
 941
 942
 943class CaseSpecificColumnConstraint(ColumnConstraintKind):
 944    arg_types = {"not_": True}
 945
 946
 947class CharacterSetColumnConstraint(ColumnConstraintKind):
 948    arg_types = {"this": True}
 949
 950
 951class CheckColumnConstraint(ColumnConstraintKind):
 952    pass
 953
 954
 955class CollateColumnConstraint(ColumnConstraintKind):
 956    pass
 957
 958
 959class CommentColumnConstraint(ColumnConstraintKind):
 960    pass
 961
 962
 963class CompressColumnConstraint(ColumnConstraintKind):
 964    pass
 965
 966
 967class DateFormatColumnConstraint(ColumnConstraintKind):
 968    arg_types = {"this": True}
 969
 970
 971class DefaultColumnConstraint(ColumnConstraintKind):
 972    pass
 973
 974
 975class EncodeColumnConstraint(ColumnConstraintKind):
 976    pass
 977
 978
 979class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
 980    # this: True -> ALWAYS, this: False -> BY DEFAULT
 981    arg_types = {
 982        "this": False,
 983        "start": False,
 984        "increment": False,
 985        "minvalue": False,
 986        "maxvalue": False,
 987        "cycle": False,
 988    }
 989
 990
 991class InlineLengthColumnConstraint(ColumnConstraintKind):
 992    pass
 993
 994
 995class NotNullColumnConstraint(ColumnConstraintKind):
 996    arg_types = {"allow_null": False}
 997
 998
 999class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1000    arg_types = {"desc": False}
1001
1002
1003class TitleColumnConstraint(ColumnConstraintKind):
1004    pass
1005
1006
1007class UniqueColumnConstraint(ColumnConstraintKind):
1008    arg_types: t.Dict[str, t.Any] = {}
1009
1010
1011class UppercaseColumnConstraint(ColumnConstraintKind):
1012    arg_types: t.Dict[str, t.Any] = {}
1013
1014
1015class PathColumnConstraint(ColumnConstraintKind):
1016    pass
1017
1018
1019class Constraint(Expression):
1020    arg_types = {"this": True, "expressions": True}
1021
1022
1023class Delete(Expression):
1024    arg_types = {"with": False, "this": False, "using": False, "where": False}
1025
1026
1027class Drop(Expression):
1028    arg_types = {
1029        "this": False,
1030        "kind": False,
1031        "exists": False,
1032        "temporary": False,
1033        "materialized": False,
1034        "cascade": False,
1035    }
1036
1037
1038class Filter(Expression):
1039    arg_types = {"this": True, "expression": True}
1040
1041
1042class Check(Expression):
1043    pass
1044
1045
1046class Directory(Expression):
1047    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1048    arg_types = {"this": True, "local": False, "row_format": False}
1049
1050
1051class ForeignKey(Expression):
1052    arg_types = {
1053        "expressions": True,
1054        "reference": False,
1055        "delete": False,
1056        "update": False,
1057    }
1058
1059
1060class PrimaryKey(Expression):
1061    arg_types = {"expressions": True, "options": False}
1062
1063
1064class Unique(Expression):
1065    arg_types = {"expressions": True}
1066
1067
1068# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1069# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1070class Into(Expression):
1071    arg_types = {"this": True, "temporary": False, "unlogged": False}
1072
1073
1074class From(Expression):
1075    arg_types = {"expressions": True}
1076
1077
1078class Having(Expression):
1079    pass
1080
1081
1082class Hint(Expression):
1083    arg_types = {"expressions": True}
1084
1085
1086class JoinHint(Expression):
1087    arg_types = {"this": True, "expressions": True}
1088
1089
1090class Identifier(Expression):
1091    arg_types = {"this": True, "quoted": False}
1092
1093    @property
1094    def quoted(self):
1095        return bool(self.args.get("quoted"))
1096
1097    def __eq__(self, other):
1098        return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this)
1099
1100    def __hash__(self):
1101        return hash((self.key, self.this.lower()))
1102
1103    @property
1104    def output_name(self):
1105        return self.name
1106
1107
1108class Index(Expression):
1109    arg_types = {
1110        "this": False,
1111        "table": False,
1112        "where": False,
1113        "columns": False,
1114        "unique": False,
1115        "primary": False,
1116        "amp": False,  # teradata
1117    }
1118
1119
1120class Insert(Expression):
1121    arg_types = {
1122        "with": False,
1123        "this": True,
1124        "expression": False,
1125        "overwrite": False,
1126        "exists": False,
1127        "partition": False,
1128        "alternative": False,
1129    }
1130
1131
1132# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1133class Introducer(Expression):
1134    arg_types = {"this": True, "expression": True}
1135
1136
1137# national char, like n'utf8'
1138class National(Expression):
1139    pass
1140
1141
1142class LoadData(Expression):
1143    arg_types = {
1144        "this": True,
1145        "local": False,
1146        "overwrite": False,
1147        "inpath": True,
1148        "partition": False,
1149        "input_format": False,
1150        "serde": False,
1151    }
1152
1153
1154class Partition(Expression):
1155    arg_types = {"expressions": True}
1156
1157
1158class Fetch(Expression):
1159    arg_types = {"direction": False, "count": False}
1160
1161
1162class Group(Expression):
1163    arg_types = {
1164        "expressions": False,
1165        "grouping_sets": False,
1166        "cube": False,
1167        "rollup": False,
1168    }
1169
1170
1171class Lambda(Expression):
1172    arg_types = {"this": True, "expressions": True}
1173
1174
1175class Limit(Expression):
1176    arg_types = {"this": False, "expression": True}
1177
1178
1179class Literal(Condition):
1180    arg_types = {"this": True, "is_string": True}
1181
1182    def __eq__(self, other):
1183        return (
1184            isinstance(other, Literal)
1185            and self.this == other.this
1186            and self.args["is_string"] == other.args["is_string"]
1187        )
1188
1189    def __hash__(self):
1190        return hash((self.key, self.this, self.args["is_string"]))
1191
1192    @classmethod
1193    def number(cls, number) -> Literal:
1194        return cls(this=str(number), is_string=False)
1195
1196    @classmethod
1197    def string(cls, string) -> Literal:
1198        return cls(this=str(string), is_string=True)
1199
1200    @property
1201    def output_name(self):
1202        return self.name
1203
1204
1205class Join(Expression):
1206    arg_types = {
1207        "this": True,
1208        "on": False,
1209        "side": False,
1210        "kind": False,
1211        "using": False,
1212        "natural": False,
1213    }
1214
1215    @property
1216    def kind(self):
1217        return self.text("kind").upper()
1218
1219    @property
1220    def side(self):
1221        return self.text("side").upper()
1222
1223    @property
1224    def alias_or_name(self):
1225        return self.this.alias_or_name
1226
1227    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1228        """
1229        Append to or set the ON expressions.
1230
1231        Example:
1232            >>> import sqlglot
1233            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1234            'JOIN x ON y = 1'
1235
1236        Args:
1237            *expressions (str | Expression): the SQL code strings to parse.
1238                If an `Expression` instance is passed, it will be used as-is.
1239                Multiple expressions are combined with an AND operator.
1240            append (bool): if `True`, AND the new expressions to any existing expression.
1241                Otherwise, this resets the expression.
1242            dialect (str): the dialect used to parse the input expressions.
1243            copy (bool): if `False`, modify this expression instance in-place.
1244            opts (kwargs): other options to use to parse the input expressions.
1245
1246        Returns:
1247            Join: the modified join expression.
1248        """
1249        join = _apply_conjunction_builder(
1250            *expressions,
1251            instance=self,
1252            arg="on",
1253            append=append,
1254            dialect=dialect,
1255            copy=copy,
1256            **opts,
1257        )
1258
1259        if join.kind == "CROSS":
1260            join.set("kind", None)
1261
1262        return join
1263
1264    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1265        """
1266        Append to or set the USING expressions.
1267
1268        Example:
1269            >>> import sqlglot
1270            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1271            'JOIN x USING (foo, bla)'
1272
1273        Args:
1274            *expressions (str | Expression): the SQL code strings to parse.
1275                If an `Expression` instance is passed, it will be used as-is.
1276            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1277                Otherwise, this resets the expression.
1278            dialect (str): the dialect used to parse the input expressions.
1279            copy (bool): if `False`, modify this expression instance in-place.
1280            opts (kwargs): other options to use to parse the input expressions.
1281
1282        Returns:
1283            Join: the modified join expression.
1284        """
1285        join = _apply_list_builder(
1286            *expressions,
1287            instance=self,
1288            arg="using",
1289            append=append,
1290            dialect=dialect,
1291            copy=copy,
1292            **opts,
1293        )
1294
1295        if join.kind == "CROSS":
1296            join.set("kind", None)
1297
1298        return join
1299
1300
1301class Lateral(UDTF):
1302    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1303
1304
1305class MatchRecognize(Expression):
1306    arg_types = {
1307        "partition_by": False,
1308        "order": False,
1309        "measures": False,
1310        "rows": False,
1311        "after": False,
1312        "pattern": False,
1313        "define": False,
1314    }
1315
1316
1317# Clickhouse FROM FINAL modifier
1318# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1319class Final(Expression):
1320    pass
1321
1322
1323class Offset(Expression):
1324    arg_types = {"this": False, "expression": True}
1325
1326
1327class Order(Expression):
1328    arg_types = {"this": False, "expressions": True}
1329
1330
1331# hive specific sorts
1332# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1333class Cluster(Order):
1334    pass
1335
1336
1337class Distribute(Order):
1338    pass
1339
1340
1341class Sort(Order):
1342    pass
1343
1344
1345class Ordered(Expression):
1346    arg_types = {"this": True, "desc": True, "nulls_first": True}
1347
1348
1349class Property(Expression):
1350    arg_types = {"this": True, "value": True}
1351
1352
1353class AlgorithmProperty(Property):
1354    arg_types = {"this": True}
1355
1356
1357class DefinerProperty(Property):
1358    arg_types = {"this": True}
1359
1360
1361class SqlSecurityProperty(Property):
1362    arg_types = {"definer": True}
1363
1364
1365class TableFormatProperty(Property):
1366    arg_types = {"this": True}
1367
1368
1369class PartitionedByProperty(Property):
1370    arg_types = {"this": True}
1371
1372
1373class FileFormatProperty(Property):
1374    arg_types = {"this": True}
1375
1376
1377class DistKeyProperty(Property):
1378    arg_types = {"this": True}
1379
1380
1381class SortKeyProperty(Property):
1382    arg_types = {"this": True, "compound": False}
1383
1384
1385class DistStyleProperty(Property):
1386    arg_types = {"this": True}
1387
1388
1389class LikeProperty(Property):
1390    arg_types = {"this": True, "expressions": False}
1391
1392
1393class LocationProperty(Property):
1394    arg_types = {"this": True}
1395
1396
1397class EngineProperty(Property):
1398    arg_types = {"this": True}
1399
1400
1401class AutoIncrementProperty(Property):
1402    arg_types = {"this": True}
1403
1404
1405class CharacterSetProperty(Property):
1406    arg_types = {"this": True, "default": True}
1407
1408
1409class CollateProperty(Property):
1410    arg_types = {"this": True}
1411
1412
1413class SchemaCommentProperty(Property):
1414    arg_types = {"this": True}
1415
1416
1417class ReturnsProperty(Property):
1418    arg_types = {"this": True, "is_table": False, "table": False}
1419
1420
1421class LanguageProperty(Property):
1422    arg_types = {"this": True}
1423
1424
1425class ExecuteAsProperty(Property):
1426    arg_types = {"this": True}
1427
1428
1429class VolatilityProperty(Property):
1430    arg_types = {"this": True}
1431
1432
1433class RowFormatDelimitedProperty(Property):
1434    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1435    arg_types = {
1436        "fields": False,
1437        "escaped": False,
1438        "collection_items": False,
1439        "map_keys": False,
1440        "lines": False,
1441        "null": False,
1442        "serde": False,
1443    }
1444
1445
1446class RowFormatSerdeProperty(Property):
1447    arg_types = {"this": True}
1448
1449
1450class SerdeProperties(Property):
1451    arg_types = {"expressions": True}
1452
1453
1454class FallbackProperty(Property):
1455    arg_types = {"no": True, "protection": False}
1456
1457
1458class WithJournalTableProperty(Property):
1459    arg_types = {"this": True}
1460
1461
1462class LogProperty(Property):
1463    arg_types = {"no": True}
1464
1465
1466class JournalProperty(Property):
1467    arg_types = {"no": True, "dual": False, "before": False}
1468
1469
1470class AfterJournalProperty(Property):
1471    arg_types = {"no": True, "dual": False, "local": False}
1472
1473
1474class ChecksumProperty(Property):
1475    arg_types = {"on": False, "default": False}
1476
1477
1478class FreespaceProperty(Property):
1479    arg_types = {"this": True, "percent": False}
1480
1481
1482class MergeBlockRatioProperty(Property):
1483    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1484
1485
1486class DataBlocksizeProperty(Property):
1487    arg_types = {"size": False, "units": False, "min": False, "default": False}
1488
1489
1490class BlockCompressionProperty(Property):
1491    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1492
1493
1494class IsolatedLoadingProperty(Property):
1495    arg_types = {
1496        "no": True,
1497        "concurrent": True,
1498        "for_all": True,
1499        "for_insert": True,
1500        "for_none": True,
1501    }
1502
1503
1504class LockingProperty(Property):
1505    arg_types = {
1506        "this": False,
1507        "kind": True,
1508        "for_or_in": True,
1509        "lock_type": True,
1510        "override": False,
1511    }
1512
1513
1514class Properties(Expression):
1515    arg_types = {"expressions": True}
1516
1517    NAME_TO_PROPERTY = {
1518        "ALGORITHM": AlgorithmProperty,
1519        "AUTO_INCREMENT": AutoIncrementProperty,
1520        "CHARACTER SET": CharacterSetProperty,
1521        "COLLATE": CollateProperty,
1522        "COMMENT": SchemaCommentProperty,
1523        "DEFINER": DefinerProperty,
1524        "DISTKEY": DistKeyProperty,
1525        "DISTSTYLE": DistStyleProperty,
1526        "ENGINE": EngineProperty,
1527        "EXECUTE AS": ExecuteAsProperty,
1528        "FORMAT": FileFormatProperty,
1529        "LANGUAGE": LanguageProperty,
1530        "LOCATION": LocationProperty,
1531        "PARTITIONED_BY": PartitionedByProperty,
1532        "RETURNS": ReturnsProperty,
1533        "SORTKEY": SortKeyProperty,
1534        "TABLE_FORMAT": TableFormatProperty,
1535    }
1536
1537    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1538
1539    # CREATE property locations
1540    # Form: schema specified
1541    #   create [POST_CREATE]
1542    #     table a [POST_NAME]
1543    #     (b int) [POST_SCHEMA]
1544    #     with ([POST_WITH])
1545    #     index (b) [POST_INDEX]
1546    #
1547    # Form: alias selection
1548    #   create [POST_CREATE]
1549    #     table a [POST_NAME]
1550    #     as [POST_ALIAS] (select * from b)
1551    #     index (c) [POST_INDEX]
1552    class Location(AutoName):
1553        POST_CREATE = auto()
1554        POST_NAME = auto()
1555        POST_SCHEMA = auto()
1556        POST_WITH = auto()
1557        POST_ALIAS = auto()
1558        POST_INDEX = auto()
1559        UNSUPPORTED = auto()
1560
1561    @classmethod
1562    def from_dict(cls, properties_dict) -> Properties:
1563        expressions = []
1564        for key, value in properties_dict.items():
1565            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1566            if property_cls:
1567                expressions.append(property_cls(this=convert(value)))
1568            else:
1569                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1570
1571        return cls(expressions=expressions)
1572
1573
1574class Qualify(Expression):
1575    pass
1576
1577
1578# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1579class Return(Expression):
1580    pass
1581
1582
1583class Reference(Expression):
1584    arg_types = {"this": True, "expressions": False, "options": False}
1585
1586
1587class Tuple(Expression):
1588    arg_types = {"expressions": False}
1589
1590
1591class Subqueryable(Unionable):
1592    def subquery(self, alias=None, copy=True) -> Subquery:
1593        """
1594        Convert this expression to an aliased expression that can be used as a Subquery.
1595
1596        Example:
1597            >>> subquery = Select().select("x").from_("tbl").subquery()
1598            >>> Select().select("x").from_(subquery).sql()
1599            'SELECT x FROM (SELECT x FROM tbl)'
1600
1601        Args:
1602            alias (str | Identifier): an optional alias for the subquery
1603            copy (bool): if `False`, modify this expression instance in-place.
1604
1605        Returns:
1606            Alias: the subquery
1607        """
1608        instance = _maybe_copy(self, copy)
1609        return Subquery(
1610            this=instance,
1611            alias=TableAlias(this=to_identifier(alias)),
1612        )
1613
1614    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1615        raise NotImplementedError
1616
1617    @property
1618    def ctes(self):
1619        with_ = self.args.get("with")
1620        if not with_:
1621            return []
1622        return with_.expressions
1623
1624    @property
1625    def selects(self):
1626        raise NotImplementedError("Subqueryable objects must implement `selects`")
1627
1628    @property
1629    def named_selects(self):
1630        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1631
1632    def with_(
1633        self,
1634        alias,
1635        as_,
1636        recursive=None,
1637        append=True,
1638        dialect=None,
1639        copy=True,
1640        **opts,
1641    ):
1642        """
1643        Append to or set the common table expressions.
1644
1645        Example:
1646            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1647            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1648
1649        Args:
1650            alias (str | Expression): the SQL code string to parse as the table name.
1651                If an `Expression` instance is passed, this is used as-is.
1652            as_ (str | Expression): the SQL code string to parse as the table expression.
1653                If an `Expression` instance is passed, it will be used as-is.
1654            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1655            append (bool): if `True`, add to any existing expressions.
1656                Otherwise, this resets the expressions.
1657            dialect (str): the dialect used to parse the input expression.
1658            copy (bool): if `False`, modify this expression instance in-place.
1659            opts (kwargs): other options to use to parse the input expressions.
1660
1661        Returns:
1662            Select: the modified expression.
1663        """
1664        alias_expression = maybe_parse(
1665            alias,
1666            dialect=dialect,
1667            into=TableAlias,
1668            **opts,
1669        )
1670        as_expression = maybe_parse(
1671            as_,
1672            dialect=dialect,
1673            **opts,
1674        )
1675        cte = CTE(
1676            this=as_expression,
1677            alias=alias_expression,
1678        )
1679        return _apply_child_list_builder(
1680            cte,
1681            instance=self,
1682            arg="with",
1683            append=append,
1684            copy=copy,
1685            into=With,
1686            properties={"recursive": recursive or False},
1687        )
1688
1689
1690QUERY_MODIFIERS = {
1691    "match": False,
1692    "laterals": False,
1693    "joins": False,
1694    "pivots": False,
1695    "where": False,
1696    "group": False,
1697    "having": False,
1698    "qualify": False,
1699    "windows": False,
1700    "distribute": False,
1701    "sort": False,
1702    "cluster": False,
1703    "order": False,
1704    "limit": False,
1705    "offset": False,
1706    "lock": False,
1707}
1708
1709
1710class Table(Expression):
1711    arg_types = {
1712        "this": True,
1713        "alias": False,
1714        "db": False,
1715        "catalog": False,
1716        "laterals": False,
1717        "joins": False,
1718        "pivots": False,
1719        "hints": False,
1720        "system_time": False,
1721    }
1722
1723    @property
1724    def db(self) -> str:
1725        return self.text("db")
1726
1727    @property
1728    def catalog(self) -> str:
1729        return self.text("catalog")
1730
1731
1732# See the TSQL "Querying data in a system-versioned temporal table" page
1733class SystemTime(Expression):
1734    arg_types = {
1735        "this": False,
1736        "expression": False,
1737        "kind": True,
1738    }
1739
1740
1741class Union(Subqueryable):
1742    arg_types = {
1743        "with": False,
1744        "this": True,
1745        "expression": True,
1746        "distinct": False,
1747        **QUERY_MODIFIERS,
1748    }
1749
1750    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1751        """
1752        Set the LIMIT expression.
1753
1754        Example:
1755            >>> select("1").union(select("1")).limit(1).sql()
1756            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1757
1758        Args:
1759            expression (str | int | Expression): the SQL code string to parse.
1760                This can also be an integer.
1761                If a `Limit` instance is passed, this is used as-is.
1762                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1763            dialect (str): the dialect used to parse the input expression.
1764            copy (bool): if `False`, modify this expression instance in-place.
1765            opts (kwargs): other options to use to parse the input expressions.
1766
1767        Returns:
1768            Select: The limited subqueryable.
1769        """
1770        return (
1771            select("*")
1772            .from_(self.subquery(alias="_l_0", copy=copy))
1773            .limit(expression, dialect=dialect, copy=False, **opts)
1774        )
1775
1776    def select(
1777        self,
1778        *expressions: str | Expression,
1779        append: bool = True,
1780        dialect: DialectType = None,
1781        copy: bool = True,
1782        **opts,
1783    ) -> Union:
1784        """Append to or set the SELECT of the union recursively.
1785
1786        Example:
1787            >>> from sqlglot import parse_one
1788            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1789            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1790
1791        Args:
1792            *expressions: the SQL code strings to parse.
1793                If an `Expression` instance is passed, it will be used as-is.
1794            append: if `True`, add to any existing expressions.
1795                Otherwise, this resets the expressions.
1796            dialect: the dialect used to parse the input expressions.
1797            copy: if `False`, modify this expression instance in-place.
1798            opts: other options to use to parse the input expressions.
1799
1800        Returns:
1801            Union: the modified expression.
1802        """
1803        this = self.copy() if copy else self
1804        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1805        this.expression.unnest().select(
1806            *expressions, append=append, dialect=dialect, copy=False, **opts
1807        )
1808        return this
1809
1810    @property
1811    def named_selects(self):
1812        return self.this.unnest().named_selects
1813
1814    @property
1815    def is_star(self) -> bool:
1816        return self.this.is_star or self.expression.is_star
1817
1818    @property
1819    def selects(self):
1820        return self.this.unnest().selects
1821
1822    @property
1823    def left(self):
1824        return self.this
1825
1826    @property
1827    def right(self):
1828        return self.expression
1829
1830
1831class Except(Union):
1832    pass
1833
1834
1835class Intersect(Union):
1836    pass
1837
1838
1839class Unnest(UDTF):
1840    arg_types = {
1841        "expressions": True,
1842        "ordinality": False,
1843        "alias": False,
1844        "offset": False,
1845    }
1846
1847
1848class Update(Expression):
1849    arg_types = {
1850        "with": False,
1851        "this": False,
1852        "expressions": True,
1853        "from": False,
1854        "where": False,
1855    }
1856
1857
1858class Values(UDTF):
1859    arg_types = {
1860        "expressions": True,
1861        "ordinality": False,
1862        "alias": False,
1863    }
1864
1865
1866class Var(Expression):
1867    pass
1868
1869
1870class Schema(Expression):
1871    arg_types = {"this": False, "expressions": False}
1872
1873
1874# Used to represent the FOR UPDATE and FOR SHARE locking read types.
1875# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
1876class Lock(Expression):
1877    arg_types = {"update": True}
1878
1879
1880class Select(Subqueryable):
1881    arg_types = {
1882        "with": False,
1883        "expressions": False,
1884        "hint": False,
1885        "distinct": False,
1886        "into": False,
1887        "from": False,
1888        **QUERY_MODIFIERS,
1889    }
1890
1891    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1892        """
1893        Set the FROM expression.
1894
1895        Example:
1896            >>> Select().from_("tbl").select("x").sql()
1897            'SELECT x FROM tbl'
1898
1899        Args:
1900            *expressions (str | Expression): the SQL code strings to parse.
1901                If a `From` instance is passed, this is used as-is.
1902                If another `Expression` instance is passed, it will be wrapped in a `From`.
1903            append (bool): if `True`, add to any existing expressions.
1904                Otherwise, this flattens all the `From` expression into a single expression.
1905            dialect (str): the dialect used to parse the input expression.
1906            copy (bool): if `False`, modify this expression instance in-place.
1907            opts (kwargs): other options to use to parse the input expressions.
1908
1909        Returns:
1910            Select: the modified expression.
1911        """
1912        return _apply_child_list_builder(
1913            *expressions,
1914            instance=self,
1915            arg="from",
1916            append=append,
1917            copy=copy,
1918            prefix="FROM",
1919            into=From,
1920            dialect=dialect,
1921            **opts,
1922        )
1923
1924    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1925        """
1926        Set the GROUP BY expression.
1927
1928        Example:
1929            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1930            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1931
1932        Args:
1933            *expressions (str | Expression): the SQL code strings to parse.
1934                If a `Group` instance is passed, this is used as-is.
1935                If another `Expression` instance is passed, it will be wrapped in a `Group`.
1936                If nothing is passed in then a group by is not applied to the expression
1937            append (bool): if `True`, add to any existing expressions.
1938                Otherwise, this flattens all the `Group` expression into a single expression.
1939            dialect (str): the dialect used to parse the input expression.
1940            copy (bool): if `False`, modify this expression instance in-place.
1941            opts (kwargs): other options to use to parse the input expressions.
1942
1943        Returns:
1944            Select: the modified expression.
1945        """
1946        if not expressions:
1947            return self if not copy else self.copy()
1948        return _apply_child_list_builder(
1949            *expressions,
1950            instance=self,
1951            arg="group",
1952            append=append,
1953            copy=copy,
1954            prefix="GROUP BY",
1955            into=Group,
1956            dialect=dialect,
1957            **opts,
1958        )
1959
1960    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1961        """
1962        Set the ORDER BY expression.
1963
1964        Example:
1965            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1966            'SELECT x FROM tbl ORDER BY x DESC'
1967
1968        Args:
1969            *expressions (str | Expression): the SQL code strings to parse.
1970                If a `Group` instance is passed, this is used as-is.
1971                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1972            append (bool): if `True`, add to any existing expressions.
1973                Otherwise, this flattens all the `Order` expression into a single expression.
1974            dialect (str): the dialect used to parse the input expression.
1975            copy (bool): if `False`, modify this expression instance in-place.
1976            opts (kwargs): other options to use to parse the input expressions.
1977
1978        Returns:
1979            Select: the modified expression.
1980        """
1981        return _apply_child_list_builder(
1982            *expressions,
1983            instance=self,
1984            arg="order",
1985            append=append,
1986            copy=copy,
1987            prefix="ORDER BY",
1988            into=Order,
1989            dialect=dialect,
1990            **opts,
1991        )
1992
1993    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1994        """
1995        Set the SORT BY expression.
1996
1997        Example:
1998            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
1999            'SELECT x FROM tbl SORT BY x DESC'
2000
2001        Args:
2002            *expressions (str | Expression): the SQL code strings to parse.
2003                If a `Group` instance is passed, this is used as-is.
2004                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2005            append (bool): if `True`, add to any existing expressions.
2006                Otherwise, this flattens all the `Order` expression into a single expression.
2007            dialect (str): the dialect used to parse the input expression.
2008            copy (bool): if `False`, modify this expression instance in-place.
2009            opts (kwargs): other options to use to parse the input expressions.
2010
2011        Returns:
2012            Select: the modified expression.
2013        """
2014        return _apply_child_list_builder(
2015            *expressions,
2016            instance=self,
2017            arg="sort",
2018            append=append,
2019            copy=copy,
2020            prefix="SORT BY",
2021            into=Sort,
2022            dialect=dialect,
2023            **opts,
2024        )
2025
2026    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2027        """
2028        Set the CLUSTER BY expression.
2029
2030        Example:
2031            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2032            'SELECT x FROM tbl CLUSTER BY x DESC'
2033
2034        Args:
2035            *expressions (str | Expression): the SQL code strings to parse.
2036                If a `Group` instance is passed, this is used as-is.
2037                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2038            append (bool): if `True`, add to any existing expressions.
2039                Otherwise, this flattens all the `Order` expression into a single expression.
2040            dialect (str): the dialect used to parse the input expression.
2041            copy (bool): if `False`, modify this expression instance in-place.
2042            opts (kwargs): other options to use to parse the input expressions.
2043
2044        Returns:
2045            Select: the modified expression.
2046        """
2047        return _apply_child_list_builder(
2048            *expressions,
2049            instance=self,
2050            arg="cluster",
2051            append=append,
2052            copy=copy,
2053            prefix="CLUSTER BY",
2054            into=Cluster,
2055            dialect=dialect,
2056            **opts,
2057        )
2058
2059    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2060        """
2061        Set the LIMIT expression.
2062
2063        Example:
2064            >>> Select().from_("tbl").select("x").limit(10).sql()
2065            'SELECT x FROM tbl LIMIT 10'
2066
2067        Args:
2068            expression (str | int | Expression): the SQL code string to parse.
2069                This can also be an integer.
2070                If a `Limit` instance is passed, this is used as-is.
2071                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2072            dialect (str): the dialect used to parse the input expression.
2073            copy (bool): if `False`, modify this expression instance in-place.
2074            opts (kwargs): other options to use to parse the input expressions.
2075
2076        Returns:
2077            Select: the modified expression.
2078        """
2079        return _apply_builder(
2080            expression=expression,
2081            instance=self,
2082            arg="limit",
2083            into=Limit,
2084            prefix="LIMIT",
2085            dialect=dialect,
2086            copy=copy,
2087            **opts,
2088        )
2089
2090    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2091        """
2092        Set the OFFSET expression.
2093
2094        Example:
2095            >>> Select().from_("tbl").select("x").offset(10).sql()
2096            'SELECT x FROM tbl OFFSET 10'
2097
2098        Args:
2099            expression (str | int | Expression): the SQL code string to parse.
2100                This can also be an integer.
2101                If a `Offset` instance is passed, this is used as-is.
2102                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2103            dialect (str): the dialect used to parse the input expression.
2104            copy (bool): if `False`, modify this expression instance in-place.
2105            opts (kwargs): other options to use to parse the input expressions.
2106
2107        Returns:
2108            Select: the modified expression.
2109        """
2110        return _apply_builder(
2111            expression=expression,
2112            instance=self,
2113            arg="offset",
2114            into=Offset,
2115            prefix="OFFSET",
2116            dialect=dialect,
2117            copy=copy,
2118            **opts,
2119        )
2120
2121    def select(
2122        self,
2123        *expressions: str | Expression,
2124        append: bool = True,
2125        dialect: DialectType = None,
2126        copy: bool = True,
2127        **opts,
2128    ) -> Select:
2129        """
2130        Append to or set the SELECT expressions.
2131
2132        Example:
2133            >>> Select().select("x", "y").sql()
2134            'SELECT x, y'
2135
2136        Args:
2137            *expressions: the SQL code strings to parse.
2138                If an `Expression` instance is passed, it will be used as-is.
2139            append: if `True`, add to any existing expressions.
2140                Otherwise, this resets the expressions.
2141            dialect: the dialect used to parse the input expressions.
2142            copy: if `False`, modify this expression instance in-place.
2143            opts: other options to use to parse the input expressions.
2144
2145        Returns:
2146            Select: the modified expression.
2147        """
2148        return _apply_list_builder(
2149            *expressions,
2150            instance=self,
2151            arg="expressions",
2152            append=append,
2153            dialect=dialect,
2154            copy=copy,
2155            **opts,
2156        )
2157
2158    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2159        """
2160        Append to or set the LATERAL expressions.
2161
2162        Example:
2163            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2164            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2165
2166        Args:
2167            *expressions (str | Expression): the SQL code strings to parse.
2168                If an `Expression` instance is passed, it will be used as-is.
2169            append (bool): if `True`, add to any existing expressions.
2170                Otherwise, this resets the expressions.
2171            dialect (str): the dialect used to parse the input expressions.
2172            copy (bool): if `False`, modify this expression instance in-place.
2173            opts (kwargs): other options to use to parse the input expressions.
2174
2175        Returns:
2176            Select: the modified expression.
2177        """
2178        return _apply_list_builder(
2179            *expressions,
2180            instance=self,
2181            arg="laterals",
2182            append=append,
2183            into=Lateral,
2184            prefix="LATERAL VIEW",
2185            dialect=dialect,
2186            copy=copy,
2187            **opts,
2188        )
2189
2190    def join(
2191        self,
2192        expression,
2193        on=None,
2194        using=None,
2195        append=True,
2196        join_type=None,
2197        join_alias=None,
2198        dialect=None,
2199        copy=True,
2200        **opts,
2201    ) -> Select:
2202        """
2203        Append to or set the JOIN expressions.
2204
2205        Example:
2206            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2207            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2208
2209            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2210            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2211
2212            Use `join_type` to change the type of join:
2213
2214            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2215            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2216
2217        Args:
2218            expression (str | Expression): the SQL code string to parse.
2219                If an `Expression` instance is passed, it will be used as-is.
2220            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2221                If an `Expression` instance is passed, it will be used as-is.
2222            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2223                If an `Expression` instance is passed, it will be used as-is.
2224            append (bool): if `True`, add to any existing expressions.
2225                Otherwise, this resets the expressions.
2226            join_type (str): If set, alter the parsed join type
2227            dialect (str): the dialect used to parse the input expressions.
2228            copy (bool): if `False`, modify this expression instance in-place.
2229            opts (kwargs): other options to use to parse the input expressions.
2230
2231        Returns:
2232            Select: the modified expression.
2233        """
2234        parse_args = {"dialect": dialect, **opts}
2235
2236        try:
2237            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2238        except ParseError:
2239            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2240
2241        join = expression if isinstance(expression, Join) else Join(this=expression)
2242
2243        if isinstance(join.this, Select):
2244            join.this.replace(join.this.subquery())
2245
2246        if join_type:
2247            natural: t.Optional[Token]
2248            side: t.Optional[Token]
2249            kind: t.Optional[Token]
2250
2251            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2252
2253            if natural:
2254                join.set("natural", True)
2255            if side:
2256                join.set("side", side.text)
2257            if kind:
2258                join.set("kind", kind.text)
2259
2260        if on:
2261            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2262            join.set("on", on)
2263
2264        if using:
2265            join = _apply_list_builder(
2266                *ensure_collection(using),
2267                instance=join,
2268                arg="using",
2269                append=append,
2270                copy=copy,
2271                **opts,
2272            )
2273
2274        if join_alias:
2275            join.set("this", alias_(join.this, join_alias, table=True))
2276        return _apply_list_builder(
2277            join,
2278            instance=self,
2279            arg="joins",
2280            append=append,
2281            copy=copy,
2282            **opts,
2283        )
2284
2285    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2286        """
2287        Append to or set the WHERE expressions.
2288
2289        Example:
2290            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2291            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2292
2293        Args:
2294            *expressions (str | Expression): the SQL code strings to parse.
2295                If an `Expression` instance is passed, it will be used as-is.
2296                Multiple expressions are combined with an AND operator.
2297            append (bool): if `True`, AND the new expressions to any existing expression.
2298                Otherwise, this resets the expression.
2299            dialect (str): the dialect used to parse the input expressions.
2300            copy (bool): if `False`, modify this expression instance in-place.
2301            opts (kwargs): other options to use to parse the input expressions.
2302
2303        Returns:
2304            Select: the modified expression.
2305        """
2306        return _apply_conjunction_builder(
2307            *expressions,
2308            instance=self,
2309            arg="where",
2310            append=append,
2311            into=Where,
2312            dialect=dialect,
2313            copy=copy,
2314            **opts,
2315        )
2316
2317    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2318        """
2319        Append to or set the HAVING expressions.
2320
2321        Example:
2322            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2323            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2324
2325        Args:
2326            *expressions (str | Expression): the SQL code strings to parse.
2327                If an `Expression` instance is passed, it will be used as-is.
2328                Multiple expressions are combined with an AND operator.
2329            append (bool): if `True`, AND the new expressions to any existing expression.
2330                Otherwise, this resets the expression.
2331            dialect (str): the dialect used to parse the input expressions.
2332            copy (bool): if `False`, modify this expression instance in-place.
2333            opts (kwargs): other options to use to parse the input expressions.
2334
2335        Returns:
2336            Select: the modified expression.
2337        """
2338        return _apply_conjunction_builder(
2339            *expressions,
2340            instance=self,
2341            arg="having",
2342            append=append,
2343            into=Having,
2344            dialect=dialect,
2345            copy=copy,
2346            **opts,
2347        )
2348
2349    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2350        return _apply_list_builder(
2351            *expressions,
2352            instance=self,
2353            arg="windows",
2354            append=append,
2355            into=Window,
2356            dialect=dialect,
2357            copy=copy,
2358            **opts,
2359        )
2360
2361    def distinct(self, distinct=True, copy=True) -> Select:
2362        """
2363        Set the OFFSET expression.
2364
2365        Example:
2366            >>> Select().from_("tbl").select("x").distinct().sql()
2367            'SELECT DISTINCT x FROM tbl'
2368
2369        Args:
2370            distinct (bool): whether the Select should be distinct
2371            copy (bool): if `False`, modify this expression instance in-place.
2372
2373        Returns:
2374            Select: the modified expression.
2375        """
2376        instance = _maybe_copy(self, copy)
2377        instance.set("distinct", Distinct() if distinct else None)
2378        return instance
2379
2380    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2381        """
2382        Convert this expression to a CREATE TABLE AS statement.
2383
2384        Example:
2385            >>> Select().select("*").from_("tbl").ctas("x").sql()
2386            'CREATE TABLE x AS SELECT * FROM tbl'
2387
2388        Args:
2389            table (str | Expression): the SQL code string to parse as the table name.
2390                If another `Expression` instance is passed, it will be used as-is.
2391            properties (dict): an optional mapping of table properties
2392            dialect (str): the dialect used to parse the input table.
2393            copy (bool): if `False`, modify this expression instance in-place.
2394            opts (kwargs): other options to use to parse the input table.
2395
2396        Returns:
2397            Create: the CREATE TABLE AS expression
2398        """
2399        instance = _maybe_copy(self, copy)
2400        table_expression = maybe_parse(
2401            table,
2402            into=Table,
2403            dialect=dialect,
2404            **opts,
2405        )
2406        properties_expression = None
2407        if properties:
2408            properties_expression = Properties.from_dict(properties)
2409
2410        return Create(
2411            this=table_expression,
2412            kind="table",
2413            expression=instance,
2414            properties=properties_expression,
2415        )
2416
2417    def lock(self, update: bool = True, copy: bool = True) -> Select:
2418        """
2419        Set the locking read mode for this expression.
2420
2421        Examples:
2422            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2423            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2424
2425            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2426            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2427
2428        Args:
2429            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2430            copy: if `False`, modify this expression instance in-place.
2431
2432        Returns:
2433            The modified expression.
2434        """
2435
2436        inst = _maybe_copy(self, copy)
2437        inst.set("lock", Lock(update=update))
2438
2439        return inst
2440
2441    @property
2442    def named_selects(self) -> t.List[str]:
2443        return [e.output_name for e in self.expressions if e.alias_or_name]
2444
2445    @property
2446    def is_star(self) -> bool:
2447        return any(expression.is_star for expression in self.expressions)
2448
2449    @property
2450    def selects(self) -> t.List[Expression]:
2451        return self.expressions
2452
2453
2454class Subquery(DerivedTable, Unionable):
2455    arg_types = {
2456        "this": True,
2457        "alias": False,
2458        "with": False,
2459        **QUERY_MODIFIERS,
2460    }
2461
2462    def unnest(self):
2463        """
2464        Returns the first non subquery.
2465        """
2466        expression = self
2467        while isinstance(expression, Subquery):
2468            expression = expression.this
2469        return expression
2470
2471    @property
2472    def is_star(self) -> bool:
2473        return self.this.is_star
2474
2475    @property
2476    def output_name(self):
2477        return self.alias
2478
2479
2480class TableSample(Expression):
2481    arg_types = {
2482        "this": False,
2483        "method": False,
2484        "bucket_numerator": False,
2485        "bucket_denominator": False,
2486        "bucket_field": False,
2487        "percent": False,
2488        "rows": False,
2489        "size": False,
2490        "seed": False,
2491    }
2492
2493
2494class Tag(Expression):
2495    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2496
2497    arg_types = {
2498        "this": False,
2499        "prefix": False,
2500        "postfix": False,
2501    }
2502
2503
2504class Pivot(Expression):
2505    arg_types = {
2506        "this": False,
2507        "expressions": True,
2508        "field": True,
2509        "unpivot": True,
2510    }
2511
2512
2513class Window(Expression):
2514    arg_types = {
2515        "this": True,
2516        "partition_by": False,
2517        "order": False,
2518        "spec": False,
2519        "alias": False,
2520    }
2521
2522
2523class WindowSpec(Expression):
2524    arg_types = {
2525        "kind": False,
2526        "start": False,
2527        "start_side": False,
2528        "end": False,
2529        "end_side": False,
2530    }
2531
2532
2533class Where(Expression):
2534    pass
2535
2536
2537class Star(Expression):
2538    arg_types = {"except": False, "replace": False}
2539
2540    @property
2541    def name(self) -> str:
2542        return "*"
2543
2544    @property
2545    def output_name(self):
2546        return self.name
2547
2548
2549class Parameter(Expression):
2550    arg_types = {"this": True, "wrapped": False}
2551
2552
2553class SessionParameter(Expression):
2554    arg_types = {"this": True, "kind": False}
2555
2556
2557class Placeholder(Expression):
2558    arg_types = {"this": False}
2559
2560
2561class Null(Condition):
2562    arg_types: t.Dict[str, t.Any] = {}
2563
2564    @property
2565    def name(self) -> str:
2566        return "NULL"
2567
2568
2569class Boolean(Condition):
2570    pass
2571
2572
2573class DataType(Expression):
2574    arg_types = {
2575        "this": True,
2576        "expressions": False,
2577        "nested": False,
2578        "values": False,
2579        "prefix": False,
2580    }
2581
2582    class Type(AutoName):
2583        CHAR = auto()
2584        NCHAR = auto()
2585        VARCHAR = auto()
2586        NVARCHAR = auto()
2587        TEXT = auto()
2588        MEDIUMTEXT = auto()
2589        LONGTEXT = auto()
2590        MEDIUMBLOB = auto()
2591        LONGBLOB = auto()
2592        BINARY = auto()
2593        VARBINARY = auto()
2594        INT = auto()
2595        TINYINT = auto()
2596        SMALLINT = auto()
2597        BIGINT = auto()
2598        FLOAT = auto()
2599        DOUBLE = auto()
2600        DECIMAL = auto()
2601        BOOLEAN = auto()
2602        JSON = auto()
2603        JSONB = auto()
2604        INTERVAL = auto()
2605        TIME = auto()
2606        TIMESTAMP = auto()
2607        TIMESTAMPTZ = auto()
2608        TIMESTAMPLTZ = auto()
2609        DATE = auto()
2610        DATETIME = auto()
2611        ARRAY = auto()
2612        MAP = auto()
2613        UUID = auto()
2614        GEOGRAPHY = auto()
2615        GEOMETRY = auto()
2616        STRUCT = auto()
2617        NULLABLE = auto()
2618        HLLSKETCH = auto()
2619        HSTORE = auto()
2620        SUPER = auto()
2621        SERIAL = auto()
2622        SMALLSERIAL = auto()
2623        BIGSERIAL = auto()
2624        XML = auto()
2625        UNIQUEIDENTIFIER = auto()
2626        MONEY = auto()
2627        SMALLMONEY = auto()
2628        ROWVERSION = auto()
2629        IMAGE = auto()
2630        VARIANT = auto()
2631        OBJECT = auto()
2632        NULL = auto()
2633        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2634
2635    TEXT_TYPES = {
2636        Type.CHAR,
2637        Type.NCHAR,
2638        Type.VARCHAR,
2639        Type.NVARCHAR,
2640        Type.TEXT,
2641    }
2642
2643    INTEGER_TYPES = {
2644        Type.INT,
2645        Type.TINYINT,
2646        Type.SMALLINT,
2647        Type.BIGINT,
2648    }
2649
2650    FLOAT_TYPES = {
2651        Type.FLOAT,
2652        Type.DOUBLE,
2653    }
2654
2655    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2656
2657    TEMPORAL_TYPES = {
2658        Type.TIMESTAMP,
2659        Type.TIMESTAMPTZ,
2660        Type.TIMESTAMPLTZ,
2661        Type.DATE,
2662        Type.DATETIME,
2663    }
2664
2665    @classmethod
2666    def build(
2667        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2668    ) -> DataType:
2669        from sqlglot import parse_one
2670
2671        if isinstance(dtype, str):
2672            if dtype.upper() in cls.Type.__members__:
2673                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2674            else:
2675                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2676            if data_type_exp is None:
2677                raise ValueError(f"Unparsable data type value: {dtype}")
2678        elif isinstance(dtype, DataType.Type):
2679            data_type_exp = DataType(this=dtype)
2680        elif isinstance(dtype, DataType):
2681            return dtype
2682        else:
2683            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2684        return DataType(**{**data_type_exp.args, **kwargs})
2685
2686    def is_type(self, dtype: DataType.Type) -> bool:
2687        return self.this == dtype
2688
2689
2690# https://www.postgresql.org/docs/15/datatype-pseudo.html
2691class PseudoType(Expression):
2692    pass
2693
2694
2695class StructKwarg(Expression):
2696    arg_types = {"this": True, "expression": True}
2697
2698
2699# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2700class SubqueryPredicate(Predicate):
2701    pass
2702
2703
2704class All(SubqueryPredicate):
2705    pass
2706
2707
2708class Any(SubqueryPredicate):
2709    pass
2710
2711
2712class Exists(SubqueryPredicate):
2713    pass
2714
2715
2716# Commands to interact with the databases or engines. For most of the command
2717# expressions we parse whatever comes after the command's name as a string.
2718class Command(Expression):
2719    arg_types = {"this": True, "expression": False}
2720
2721
2722class Transaction(Expression):
2723    arg_types = {"this": False, "modes": False}
2724
2725
2726class Commit(Expression):
2727    arg_types = {"chain": False}
2728
2729
2730class Rollback(Expression):
2731    arg_types = {"savepoint": False}
2732
2733
2734class AlterTable(Expression):
2735    arg_types = {"this": True, "actions": True, "exists": False}
2736
2737
2738class AddConstraint(Expression):
2739    arg_types = {"this": False, "expression": False, "enforced": False}
2740
2741
2742class DropPartition(Expression):
2743    arg_types = {"expressions": True, "exists": False}
2744
2745
2746# Binary expressions like (ADD a b)
2747class Binary(Expression):
2748    arg_types = {"this": True, "expression": True}
2749
2750    @property
2751    def left(self):
2752        return self.this
2753
2754    @property
2755    def right(self):
2756        return self.expression
2757
2758
2759class Add(Binary):
2760    pass
2761
2762
2763class Connector(Binary, Condition):
2764    pass
2765
2766
2767class And(Connector):
2768    pass
2769
2770
2771class Or(Connector):
2772    pass
2773
2774
2775class BitwiseAnd(Binary):
2776    pass
2777
2778
2779class BitwiseLeftShift(Binary):
2780    pass
2781
2782
2783class BitwiseOr(Binary):
2784    pass
2785
2786
2787class BitwiseRightShift(Binary):
2788    pass
2789
2790
2791class BitwiseXor(Binary):
2792    pass
2793
2794
2795class Div(Binary):
2796    pass
2797
2798
2799class Dot(Binary):
2800    @property
2801    def name(self) -> str:
2802        return self.expression.name
2803
2804
2805class DPipe(Binary):
2806    pass
2807
2808
2809class EQ(Binary, Predicate):
2810    pass
2811
2812
2813class NullSafeEQ(Binary, Predicate):
2814    pass
2815
2816
2817class NullSafeNEQ(Binary, Predicate):
2818    pass
2819
2820
2821class Distance(Binary):
2822    pass
2823
2824
2825class Escape(Binary):
2826    pass
2827
2828
2829class Glob(Binary, Predicate):
2830    pass
2831
2832
2833class GT(Binary, Predicate):
2834    pass
2835
2836
2837class GTE(Binary, Predicate):
2838    pass
2839
2840
2841class ILike(Binary, Predicate):
2842    pass
2843
2844
2845class ILikeAny(Binary, Predicate):
2846    pass
2847
2848
2849class IntDiv(Binary):
2850    pass
2851
2852
2853class Is(Binary, Predicate):
2854    pass
2855
2856
2857class Kwarg(Binary):
2858    """Kwarg in special functions like func(kwarg => y)."""
2859
2860
2861class Like(Binary, Predicate):
2862    pass
2863
2864
2865class LikeAny(Binary, Predicate):
2866    pass
2867
2868
2869class LT(Binary, Predicate):
2870    pass
2871
2872
2873class LTE(Binary, Predicate):
2874    pass
2875
2876
2877class Mod(Binary):
2878    pass
2879
2880
2881class Mul(Binary):
2882    pass
2883
2884
2885class NEQ(Binary, Predicate):
2886    pass
2887
2888
2889class SimilarTo(Binary, Predicate):
2890    pass
2891
2892
2893class Slice(Binary):
2894    arg_types = {"this": False, "expression": False}
2895
2896
2897class Sub(Binary):
2898    pass
2899
2900
2901# Unary Expressions
2902# (NOT a)
2903class Unary(Expression):
2904    pass
2905
2906
2907class BitwiseNot(Unary):
2908    pass
2909
2910
2911class Not(Unary, Condition):
2912    pass
2913
2914
2915class Paren(Unary, Condition):
2916    arg_types = {"this": True, "with": False}
2917
2918
2919class Neg(Unary):
2920    pass
2921
2922
2923# Special Functions
2924class Alias(Expression):
2925    arg_types = {"this": True, "alias": False}
2926
2927    @property
2928    def output_name(self):
2929        return self.alias
2930
2931
2932class Aliases(Expression):
2933    arg_types = {"this": True, "expressions": True}
2934
2935    @property
2936    def aliases(self):
2937        return self.expressions
2938
2939
2940class AtTimeZone(Expression):
2941    arg_types = {"this": True, "zone": True}
2942
2943
2944class Between(Predicate):
2945    arg_types = {"this": True, "low": True, "high": True}
2946
2947
2948class Bracket(Condition):
2949    arg_types = {"this": True, "expressions": True}
2950
2951
2952class Distinct(Expression):
2953    arg_types = {"expressions": False, "on": False}
2954
2955
2956class In(Predicate):
2957    arg_types = {
2958        "this": True,
2959        "expressions": False,
2960        "query": False,
2961        "unnest": False,
2962        "field": False,
2963        "is_global": False,
2964    }
2965
2966
2967class TimeUnit(Expression):
2968    """Automatically converts unit arg into a var."""
2969
2970    arg_types = {"unit": False}
2971
2972    def __init__(self, **args):
2973        unit = args.get("unit")
2974        if isinstance(unit, Column):
2975            args["unit"] = Var(this=unit.name)
2976        elif isinstance(unit, Week):
2977            unit.set("this", Var(this=unit.this.name))
2978        super().__init__(**args)
2979
2980
2981class Interval(TimeUnit):
2982    arg_types = {"this": False, "unit": False}
2983
2984
2985class IgnoreNulls(Expression):
2986    pass
2987
2988
2989class RespectNulls(Expression):
2990    pass
2991
2992
2993# Functions
2994class Func(Condition):
2995    """
2996    The base class for all function expressions.
2997
2998    Attributes:
2999        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3000            treated as a variable length argument and the argument's value will be stored as a list.
3001        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3002            for this function expression. These values are used to map this node to a name during parsing
3003            as well as to provide the function's name during SQL string generation. By default the SQL
3004            name is set to the expression's class name transformed to snake case.
3005    """
3006
3007    is_var_len_args = False
3008
3009    @classmethod
3010    def from_arg_list(cls, args):
3011        if cls.is_var_len_args:
3012            all_arg_keys = list(cls.arg_types)
3013            # If this function supports variable length argument treat the last argument as such.
3014            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3015            num_non_var = len(non_var_len_arg_keys)
3016
3017            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3018            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3019        else:
3020            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3021
3022        return cls(**args_dict)
3023
3024    @classmethod
3025    def sql_names(cls):
3026        if cls is Func:
3027            raise NotImplementedError(
3028                "SQL name is only supported by concrete function implementations"
3029            )
3030        if "_sql_names" not in cls.__dict__:
3031            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3032        return cls._sql_names
3033
3034    @classmethod
3035    def sql_name(cls):
3036        return cls.sql_names()[0]
3037
3038    @classmethod
3039    def default_parser_mappings(cls):
3040        return {name: cls.from_arg_list for name in cls.sql_names()}
3041
3042
3043class AggFunc(Func):
3044    pass
3045
3046
3047class Abs(Func):
3048    pass
3049
3050
3051class Anonymous(Func):
3052    arg_types = {"this": True, "expressions": False}
3053    is_var_len_args = True
3054
3055
3056class ApproxDistinct(AggFunc):
3057    arg_types = {"this": True, "accuracy": False}
3058
3059
3060class Array(Func):
3061    arg_types = {"expressions": False}
3062    is_var_len_args = True
3063
3064
3065class GenerateSeries(Func):
3066    arg_types = {"start": True, "end": True, "step": False}
3067
3068
3069class ArrayAgg(AggFunc):
3070    pass
3071
3072
3073class ArrayAll(Func):
3074    arg_types = {"this": True, "expression": True}
3075
3076
3077class ArrayAny(Func):
3078    arg_types = {"this": True, "expression": True}
3079
3080
3081class ArrayConcat(Func):
3082    arg_types = {"this": True, "expressions": False}
3083    is_var_len_args = True
3084
3085
3086class ArrayContains(Func):
3087    arg_types = {"this": True, "expression": True}
3088
3089
3090class ArrayFilter(Func):
3091    arg_types = {"this": True, "expression": True}
3092    _sql_names = ["FILTER", "ARRAY_FILTER"]
3093
3094
3095class ArraySize(Func):
3096    arg_types = {"this": True, "expression": False}
3097
3098
3099class ArraySort(Func):
3100    arg_types = {"this": True, "expression": False}
3101
3102
3103class ArraySum(Func):
3104    pass
3105
3106
3107class ArrayUnionAgg(AggFunc):
3108    pass
3109
3110
3111class Avg(AggFunc):
3112    pass
3113
3114
3115class AnyValue(AggFunc):
3116    pass
3117
3118
3119class Case(Func):
3120    arg_types = {"this": False, "ifs": True, "default": False}
3121
3122
3123class Cast(Func):
3124    arg_types = {"this": True, "to": True}
3125
3126    @property
3127    def name(self) -> str:
3128        return self.this.name
3129
3130    @property
3131    def to(self):
3132        return self.args["to"]
3133
3134    @property
3135    def output_name(self):
3136        return self.name
3137
3138    def is_type(self, dtype: DataType.Type) -> bool:
3139        return self.to.is_type(dtype)
3140
3141
3142class Collate(Binary):
3143    pass
3144
3145
3146class TryCast(Cast):
3147    pass
3148
3149
3150class Ceil(Func):
3151    arg_types = {"this": True, "decimals": False}
3152    _sql_names = ["CEIL", "CEILING"]
3153
3154
3155class Coalesce(Func):
3156    arg_types = {"this": True, "expressions": False}
3157    is_var_len_args = True
3158
3159
3160class Concat(Func):
3161    arg_types = {"expressions": True}
3162    is_var_len_args = True
3163
3164
3165class ConcatWs(Concat):
3166    _sql_names = ["CONCAT_WS"]
3167
3168
3169class Count(AggFunc):
3170    arg_types = {"this": False}
3171
3172
3173class CurrentDate(Func):
3174    arg_types = {"this": False}
3175
3176
3177class CurrentDatetime(Func):
3178    arg_types = {"this": False}
3179
3180
3181class CurrentTime(Func):
3182    arg_types = {"this": False}
3183
3184
3185class CurrentTimestamp(Func):
3186    arg_types = {"this": False}
3187
3188
3189class DateAdd(Func, TimeUnit):
3190    arg_types = {"this": True, "expression": True, "unit": False}
3191
3192
3193class DateSub(Func, TimeUnit):
3194    arg_types = {"this": True, "expression": True, "unit": False}
3195
3196
3197class DateDiff(Func, TimeUnit):
3198    arg_types = {"this": True, "expression": True, "unit": False}
3199
3200
3201class DateTrunc(Func):
3202    arg_types = {"unit": True, "this": True, "zone": False}
3203
3204
3205class DatetimeAdd(Func, TimeUnit):
3206    arg_types = {"this": True, "expression": True, "unit": False}
3207
3208
3209class DatetimeSub(Func, TimeUnit):
3210    arg_types = {"this": True, "expression": True, "unit": False}
3211
3212
3213class DatetimeDiff(Func, TimeUnit):
3214    arg_types = {"this": True, "expression": True, "unit": False}
3215
3216
3217class DatetimeTrunc(Func, TimeUnit):
3218    arg_types = {"this": True, "unit": True, "zone": False}
3219
3220
3221class DayOfWeek(Func):
3222    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3223
3224
3225class DayOfMonth(Func):
3226    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3227
3228
3229class DayOfYear(Func):
3230    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3231
3232
3233class WeekOfYear(Func):
3234    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3235
3236
3237class LastDateOfMonth(Func):
3238    pass
3239
3240
3241class Extract(Func):
3242    arg_types = {"this": True, "expression": True}
3243
3244
3245class TimestampAdd(Func, TimeUnit):
3246    arg_types = {"this": True, "expression": True, "unit": False}
3247
3248
3249class TimestampSub(Func, TimeUnit):
3250    arg_types = {"this": True, "expression": True, "unit": False}
3251
3252
3253class TimestampDiff(Func, TimeUnit):
3254    arg_types = {"this": True, "expression": True, "unit": False}
3255
3256
3257class TimestampTrunc(Func, TimeUnit):
3258    arg_types = {"this": True, "unit": True, "zone": False}
3259
3260
3261class TimeAdd(Func, TimeUnit):
3262    arg_types = {"this": True, "expression": True, "unit": False}
3263
3264
3265class TimeSub(Func, TimeUnit):
3266    arg_types = {"this": True, "expression": True, "unit": False}
3267
3268
3269class TimeDiff(Func, TimeUnit):
3270    arg_types = {"this": True, "expression": True, "unit": False}
3271
3272
3273class TimeTrunc(Func, TimeUnit):
3274    arg_types = {"this": True, "unit": True, "zone": False}
3275
3276
3277class DateFromParts(Func):
3278    _sql_names = ["DATEFROMPARTS"]
3279    arg_types = {"year": True, "month": True, "day": True}
3280
3281
3282class DateStrToDate(Func):
3283    pass
3284
3285
3286class DateToDateStr(Func):
3287    pass
3288
3289
3290class DateToDi(Func):
3291    pass
3292
3293
3294class Day(Func):
3295    pass
3296
3297
3298class Decode(Func):
3299    arg_types = {"this": True, "charset": True, "replace": False}
3300
3301
3302class DiToDate(Func):
3303    pass
3304
3305
3306class Encode(Func):
3307    arg_types = {"this": True, "charset": True}
3308
3309
3310class Exp(Func):
3311    pass
3312
3313
3314class Explode(Func):
3315    pass
3316
3317
3318class Floor(Func):
3319    arg_types = {"this": True, "decimals": False}
3320
3321
3322class Greatest(Func):
3323    arg_types = {"this": True, "expressions": False}
3324    is_var_len_args = True
3325
3326
3327class GroupConcat(Func):
3328    arg_types = {"this": True, "separator": False}
3329
3330
3331class Hex(Func):
3332    pass
3333
3334
3335class If(Func):
3336    arg_types = {"this": True, "true": True, "false": False}
3337
3338
3339class IfNull(Func):
3340    arg_types = {"this": True, "expression": False}
3341    _sql_names = ["IFNULL", "NVL"]
3342
3343
3344class Initcap(Func):
3345    pass
3346
3347
3348class JSONBContains(Binary):
3349    _sql_names = ["JSONB_CONTAINS"]
3350
3351
3352class JSONExtract(Binary, Func):
3353    _sql_names = ["JSON_EXTRACT"]
3354
3355
3356class JSONExtractScalar(JSONExtract):
3357    _sql_names = ["JSON_EXTRACT_SCALAR"]
3358
3359
3360class JSONBExtract(JSONExtract):
3361    _sql_names = ["JSONB_EXTRACT"]
3362
3363
3364class JSONBExtractScalar(JSONExtract):
3365    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3366
3367
3368class Least(Func):
3369    arg_types = {"this": True, "expressions": False}
3370    is_var_len_args = True
3371
3372
3373class Length(Func):
3374    pass
3375
3376
3377class Levenshtein(Func):
3378    arg_types = {
3379        "this": True,
3380        "expression": False,
3381        "ins_cost": False,
3382        "del_cost": False,
3383        "sub_cost": False,
3384    }
3385
3386
3387class Ln(Func):
3388    pass
3389
3390
3391class Log(Func):
3392    arg_types = {"this": True, "expression": False}
3393
3394
3395class Log2(Func):
3396    pass
3397
3398
3399class Log10(Func):
3400    pass
3401
3402
3403class LogicalOr(AggFunc):
3404    _sql_names = ["LOGICAL_OR", "BOOL_OR"]
3405
3406
3407class Lower(Func):
3408    _sql_names = ["LOWER", "LCASE"]
3409
3410
3411class Map(Func):
3412    arg_types = {"keys": False, "values": False}
3413
3414
3415class VarMap(Func):
3416    arg_types = {"keys": True, "values": True}
3417    is_var_len_args = True
3418
3419
3420class Matches(Func):
3421    """Oracle/Snowflake decode.
3422    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3423    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3424    """
3425
3426    arg_types = {"this": True, "expressions": True}
3427    is_var_len_args = True
3428
3429
3430class Max(AggFunc):
3431    arg_types = {"this": True, "expression": False}
3432
3433
3434class Min(AggFunc):
3435    arg_types = {"this": True, "expression": False}
3436
3437
3438class Month(Func):
3439    pass
3440
3441
3442class Nvl2(Func):
3443    arg_types = {"this": True, "true": True, "false": False}
3444
3445
3446class Posexplode(Func):
3447    pass
3448
3449
3450class Pow(Binary, Func):
3451    _sql_names = ["POWER", "POW"]
3452
3453
3454class PercentileCont(AggFunc):
3455    pass
3456
3457
3458class PercentileDisc(AggFunc):
3459    pass
3460
3461
3462class Quantile(AggFunc):
3463    arg_types = {"this": True, "quantile": True}
3464
3465
3466# Clickhouse-specific:
3467# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3468class Quantiles(AggFunc):
3469    arg_types = {"parameters": True, "expressions": True}
3470
3471
3472class QuantileIf(AggFunc):
3473    arg_types = {"parameters": True, "expressions": True}
3474
3475
3476class ApproxQuantile(Quantile):
3477    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3478
3479
3480class ReadCSV(Func):
3481    _sql_names = ["READ_CSV"]
3482    is_var_len_args = True
3483    arg_types = {"this": True, "expressions": False}
3484
3485
3486class Reduce(Func):
3487    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3488
3489
3490class RegexpExtract(Func):
3491    arg_types = {
3492        "this": True,
3493        "expression": True,
3494        "position": False,
3495        "occurrence": False,
3496        "group": False,
3497    }
3498
3499
3500class RegexpLike(Func):
3501    arg_types = {"this": True, "expression": True, "flag": False}
3502
3503
3504class RegexpILike(Func):
3505    arg_types = {"this": True, "expression": True, "flag": False}
3506
3507
3508class RegexpSplit(Func):
3509    arg_types = {"this": True, "expression": True}
3510
3511
3512class Repeat(Func):
3513    arg_types = {"this": True, "times": True}
3514
3515
3516class Round(Func):
3517    arg_types = {"this": True, "decimals": False}
3518
3519
3520class RowNumber(Func):
3521    arg_types: t.Dict[str, t.Any] = {}
3522
3523
3524class SafeDivide(Func):
3525    arg_types = {"this": True, "expression": True}
3526
3527
3528class SetAgg(AggFunc):
3529    pass
3530
3531
3532class SortArray(Func):
3533    arg_types = {"this": True, "asc": False}
3534
3535
3536class Split(Func):
3537    arg_types = {"this": True, "expression": True, "limit": False}
3538
3539
3540# Start may be omitted in the case of postgres
3541# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3542class Substring(Func):
3543    arg_types = {"this": True, "start": False, "length": False}
3544
3545
3546class StrPosition(Func):
3547    arg_types = {
3548        "this": True,
3549        "substr": True,
3550        "position": False,
3551        "instance": False,
3552    }
3553
3554
3555class StrToDate(Func):
3556    arg_types = {"this": True, "format": True}
3557
3558
3559class StrToTime(Func):
3560    arg_types = {"this": True, "format": True}
3561
3562
3563# Spark allows unix_timestamp()
3564# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3565class StrToUnix(Func):
3566    arg_types = {"this": False, "format": False}
3567
3568
3569class NumberToStr(Func):
3570    arg_types = {"this": True, "format": True}
3571
3572
3573class Struct(Func):
3574    arg_types = {"expressions": True}
3575    is_var_len_args = True
3576
3577
3578class StructExtract(Func):
3579    arg_types = {"this": True, "expression": True}
3580
3581
3582class Sum(AggFunc):
3583    pass
3584
3585
3586class Sqrt(Func):
3587    pass
3588
3589
3590class Stddev(AggFunc):
3591    pass
3592
3593
3594class StddevPop(AggFunc):
3595    pass
3596
3597
3598class StddevSamp(AggFunc):
3599    pass
3600
3601
3602class TimeToStr(Func):
3603    arg_types = {"this": True, "format": True}
3604
3605
3606class TimeToTimeStr(Func):
3607    pass
3608
3609
3610class TimeToUnix(Func):
3611    pass
3612
3613
3614class TimeStrToDate(Func):
3615    pass
3616
3617
3618class TimeStrToTime(Func):
3619    pass
3620
3621
3622class TimeStrToUnix(Func):
3623    pass
3624
3625
3626class Trim(Func):
3627    arg_types = {
3628        "this": True,
3629        "expression": False,
3630        "position": False,
3631        "collation": False,
3632    }
3633
3634
3635class TsOrDsAdd(Func, TimeUnit):
3636    arg_types = {"this": True, "expression": True, "unit": False}
3637
3638
3639class TsOrDsToDateStr(Func):
3640    pass
3641
3642
3643class TsOrDsToDate(Func):
3644    arg_types = {"this": True, "format": False}
3645
3646
3647class TsOrDiToDi(Func):
3648    pass
3649
3650
3651class Unhex(Func):
3652    pass
3653
3654
3655class UnixToStr(Func):
3656    arg_types = {"this": True, "format": False}
3657
3658
3659# https://prestodb.io/docs/current/functions/datetime.html
3660# presto has weird zone/hours/minutes
3661class UnixToTime(Func):
3662    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3663
3664    SECONDS = Literal.string("seconds")
3665    MILLIS = Literal.string("millis")
3666    MICROS = Literal.string("micros")
3667
3668
3669class UnixToTimeStr(Func):
3670    pass
3671
3672
3673class Upper(Func):
3674    _sql_names = ["UPPER", "UCASE"]
3675
3676
3677class Variance(AggFunc):
3678    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3679
3680
3681class VariancePop(AggFunc):
3682    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3683
3684
3685class Week(Func):
3686    arg_types = {"this": True, "mode": False}
3687
3688
3689class XMLTable(Func):
3690    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3691
3692
3693class Year(Func):
3694    pass
3695
3696
3697class Use(Expression):
3698    arg_types = {"this": True, "kind": False}
3699
3700
3701class Merge(Expression):
3702    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3703
3704
3705class When(Func):
3706    arg_types = {"this": True, "then": True}
3707
3708
3709def _norm_args(expression):
3710    args = {}
3711
3712    for k, arg in expression.args.items():
3713        if isinstance(arg, list):
3714            arg = [_norm_arg(a) for a in arg]
3715            if not arg:
3716                arg = None
3717        else:
3718            arg = _norm_arg(arg)
3719
3720        if arg is not None and arg is not False:
3721            args[k] = arg
3722
3723    return args
3724
3725
3726def _norm_arg(arg):
3727    return arg.lower() if isinstance(arg, str) else arg
3728
3729
3730ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3731
3732
3733# Helpers
3734def maybe_parse(
3735    sql_or_expression: str | Expression,
3736    *,
3737    into: t.Optional[IntoType] = None,
3738    dialect: DialectType = None,
3739    prefix: t.Optional[str] = None,
3740    copy: bool = False,
3741    **opts,
3742) -> Expression:
3743    """Gracefully handle a possible string or expression.
3744
3745    Example:
3746        >>> maybe_parse("1")
3747        (LITERAL this: 1, is_string: False)
3748        >>> maybe_parse(to_identifier("x"))
3749        (IDENTIFIER this: x, quoted: False)
3750
3751    Args:
3752        sql_or_expression: the SQL code string or an expression
3753        into: the SQLGlot Expression to parse into
3754        dialect: the dialect used to parse the input expressions (in the case that an
3755            input expression is a SQL string).
3756        prefix: a string to prefix the sql with before it gets parsed
3757            (automatically includes a space)
3758        copy: whether or not to copy the expression.
3759        **opts: other options to use to parse the input expressions (again, in the case
3760            that an input expression is a SQL string).
3761
3762    Returns:
3763        Expression: the parsed or given expression.
3764    """
3765    if isinstance(sql_or_expression, Expression):
3766        if copy:
3767            return sql_or_expression.copy()
3768        return sql_or_expression
3769
3770    import sqlglot
3771
3772    sql = str(sql_or_expression)
3773    if prefix:
3774        sql = f"{prefix} {sql}"
3775    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
3776
3777
3778def _maybe_copy(instance, copy=True):
3779    return instance.copy() if copy else instance
3780
3781
3782def _is_wrong_expression(expression, into):
3783    return isinstance(expression, Expression) and not isinstance(expression, into)
3784
3785
3786def _apply_builder(
3787    expression,
3788    instance,
3789    arg,
3790    copy=True,
3791    prefix=None,
3792    into=None,
3793    dialect=None,
3794    **opts,
3795):
3796    if _is_wrong_expression(expression, into):
3797        expression = into(this=expression)
3798    instance = _maybe_copy(instance, copy)
3799    expression = maybe_parse(
3800        sql_or_expression=expression,
3801        prefix=prefix,
3802        into=into,
3803        dialect=dialect,
3804        **opts,
3805    )
3806    instance.set(arg, expression)
3807    return instance
3808
3809
3810def _apply_child_list_builder(
3811    *expressions,
3812    instance,
3813    arg,
3814    append=True,
3815    copy=True,
3816    prefix=None,
3817    into=None,
3818    dialect=None,
3819    properties=None,
3820    **opts,
3821):
3822    instance = _maybe_copy(instance, copy)
3823    parsed = []
3824    for expression in expressions:
3825        if _is_wrong_expression(expression, into):
3826            expression = into(expressions=[expression])
3827        expression = maybe_parse(
3828            expression,
3829            into=into,
3830            dialect=dialect,
3831            prefix=prefix,
3832            **opts,
3833        )
3834        parsed.extend(expression.expressions)
3835
3836    existing = instance.args.get(arg)
3837    if append and existing:
3838        parsed = existing.expressions + parsed
3839
3840    child = into(expressions=parsed)
3841    for k, v in (properties or {}).items():
3842        child.set(k, v)
3843    instance.set(arg, child)
3844    return instance
3845
3846
3847def _apply_list_builder(
3848    *expressions,
3849    instance,
3850    arg,
3851    append=True,
3852    copy=True,
3853    prefix=None,
3854    into=None,
3855    dialect=None,
3856    **opts,
3857):
3858    inst = _maybe_copy(instance, copy)
3859
3860    expressions = [
3861        maybe_parse(
3862            sql_or_expression=expression,
3863            into=into,
3864            prefix=prefix,
3865            dialect=dialect,
3866            **opts,
3867        )
3868        for expression in expressions
3869    ]
3870
3871    existing_expressions = inst.args.get(arg)
3872    if append and existing_expressions:
3873        expressions = existing_expressions + expressions
3874
3875    inst.set(arg, expressions)
3876    return inst
3877
3878
3879def _apply_conjunction_builder(
3880    *expressions,
3881    instance,
3882    arg,
3883    into=None,
3884    append=True,
3885    copy=True,
3886    dialect=None,
3887    **opts,
3888):
3889    expressions = [exp for exp in expressions if exp is not None and exp != ""]
3890    if not expressions:
3891        return instance
3892
3893    inst = _maybe_copy(instance, copy)
3894
3895    existing = inst.args.get(arg)
3896    if append and existing is not None:
3897        expressions = [existing.this if into else existing] + list(expressions)
3898
3899    node = and_(*expressions, dialect=dialect, **opts)
3900
3901    inst.set(arg, into(this=node) if into else node)
3902    return inst
3903
3904
3905def _combine(expressions, operator, dialect=None, **opts):
3906    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
3907    this = expressions[0]
3908    if expressions[1:]:
3909        this = _wrap_operator(this)
3910    for expression in expressions[1:]:
3911        this = operator(this=this, expression=_wrap_operator(expression))
3912    return this
3913
3914
3915def _wrap_operator(expression):
3916    if isinstance(expression, (And, Or, Not)):
3917        expression = Paren(this=expression)
3918    return expression
3919
3920
3921def union(left, right, distinct=True, dialect=None, **opts):
3922    """
3923    Initializes a syntax tree from one UNION expression.
3924
3925    Example:
3926        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
3927        'SELECT * FROM foo UNION SELECT * FROM bla'
3928
3929    Args:
3930        left (str | Expression): the SQL code string corresponding to the left-hand side.
3931            If an `Expression` instance is passed, it will be used as-is.
3932        right (str | Expression): the SQL code string corresponding to the right-hand side.
3933            If an `Expression` instance is passed, it will be used as-is.
3934        distinct (bool): set the DISTINCT flag if and only if this is true.
3935        dialect (str): the dialect used to parse the input expression.
3936        opts (kwargs): other options to use to parse the input expressions.
3937    Returns:
3938        Union: the syntax tree for the UNION expression.
3939    """
3940    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3941    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3942
3943    return Union(this=left, expression=right, distinct=distinct)
3944
3945
3946def intersect(left, right, distinct=True, dialect=None, **opts):
3947    """
3948    Initializes a syntax tree from one INTERSECT expression.
3949
3950    Example:
3951        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
3952        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
3953
3954    Args:
3955        left (str | Expression): the SQL code string corresponding to the left-hand side.
3956            If an `Expression` instance is passed, it will be used as-is.
3957        right (str | Expression): the SQL code string corresponding to the right-hand side.
3958            If an `Expression` instance is passed, it will be used as-is.
3959        distinct (bool): set the DISTINCT flag if and only if this is true.
3960        dialect (str): the dialect used to parse the input expression.
3961        opts (kwargs): other options to use to parse the input expressions.
3962    Returns:
3963        Intersect: the syntax tree for the INTERSECT expression.
3964    """
3965    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3966    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3967
3968    return Intersect(this=left, expression=right, distinct=distinct)
3969
3970
3971def except_(left, right, distinct=True, dialect=None, **opts):
3972    """
3973    Initializes a syntax tree from one EXCEPT expression.
3974
3975    Example:
3976        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
3977        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
3978
3979    Args:
3980        left (str | Expression): the SQL code string corresponding to the left-hand side.
3981            If an `Expression` instance is passed, it will be used as-is.
3982        right (str | Expression): the SQL code string corresponding to the right-hand side.
3983            If an `Expression` instance is passed, it will be used as-is.
3984        distinct (bool): set the DISTINCT flag if and only if this is true.
3985        dialect (str): the dialect used to parse the input expression.
3986        opts (kwargs): other options to use to parse the input expressions.
3987    Returns:
3988        Except: the syntax tree for the EXCEPT statement.
3989    """
3990    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
3991    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
3992
3993    return Except(this=left, expression=right, distinct=distinct)
3994
3995
3996def select(*expressions: str | Expression, dialect: DialectType = None, **opts) -> Select:
3997    """
3998    Initializes a syntax tree from one or multiple SELECT expressions.
3999
4000    Example:
4001        >>> select("col1", "col2").from_("tbl").sql()
4002        'SELECT col1, col2 FROM tbl'
4003
4004    Args:
4005        *expressions: the SQL code string to parse as the expressions of a
4006            SELECT statement. If an Expression instance is passed, this is used as-is.
4007        dialect: the dialect used to parse the input expressions (in the case that an
4008            input expression is a SQL string).
4009        **opts: other options to use to parse the input expressions (again, in the case
4010            that an input expression is a SQL string).
4011
4012    Returns:
4013        Select: the syntax tree for the SELECT statement.
4014    """
4015    return Select().select(*expressions, dialect=dialect, **opts)
4016
4017
4018def from_(*expressions, dialect=None, **opts) -> Select:
4019    """
4020    Initializes a syntax tree from a FROM expression.
4021
4022    Example:
4023        >>> from_("tbl").select("col1", "col2").sql()
4024        'SELECT col1, col2 FROM tbl'
4025
4026    Args:
4027        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4028            SELECT statement. If an Expression instance is passed, this is used as-is.
4029        dialect (str): the dialect used to parse the input expression (in the case that the
4030            input expression is a SQL string).
4031        **opts: other options to use to parse the input expressions (again, in the case
4032            that the input expression is a SQL string).
4033
4034    Returns:
4035        Select: the syntax tree for the SELECT statement.
4036    """
4037    return Select().from_(*expressions, dialect=dialect, **opts)
4038
4039
4040def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update:
4041    """
4042    Creates an update statement.
4043
4044    Example:
4045        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4046        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4047
4048    Args:
4049        *properties (Dict[str, Any]): dictionary of properties to set which are
4050            auto converted to sql objects eg None -> NULL
4051        where (str): sql conditional parsed into a WHERE statement
4052        from_ (str): sql statement parsed into a FROM statement
4053        dialect (str): the dialect used to parse the input expressions.
4054        **opts: other options to use to parse the input expressions.
4055
4056    Returns:
4057        Update: the syntax tree for the UPDATE statement.
4058    """
4059    update = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4060    update.set(
4061        "expressions",
4062        [
4063            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4064            for k, v in properties.items()
4065        ],
4066    )
4067    if from_:
4068        update.set(
4069            "from",
4070            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4071        )
4072    if isinstance(where, Condition):
4073        where = Where(this=where)
4074    if where:
4075        update.set(
4076            "where",
4077            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4078        )
4079    return update
4080
4081
4082def delete(table, where=None, dialect=None, **opts) -> Delete:
4083    """
4084    Builds a delete statement.
4085
4086    Example:
4087        >>> delete("my_table", where="id > 1").sql()
4088        'DELETE FROM my_table WHERE id > 1'
4089
4090    Args:
4091        where (str|Condition): sql conditional parsed into a WHERE statement
4092        dialect (str): the dialect used to parse the input expressions.
4093        **opts: other options to use to parse the input expressions.
4094
4095    Returns:
4096        Delete: the syntax tree for the DELETE statement.
4097    """
4098    return Delete(
4099        this=maybe_parse(table, into=Table, dialect=dialect, **opts),
4100        where=Where(this=where)
4101        if isinstance(where, Condition)
4102        else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4103    )
4104
4105
4106def condition(expression, dialect=None, **opts) -> Condition:
4107    """
4108    Initialize a logical condition expression.
4109
4110    Example:
4111        >>> condition("x=1").sql()
4112        'x = 1'
4113
4114        This is helpful for composing larger logical syntax trees:
4115        >>> where = condition("x=1")
4116        >>> where = where.and_("y=1")
4117        >>> Select().from_("tbl").select("*").where(where).sql()
4118        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4119
4120    Args:
4121        *expression (str | Expression): the SQL code string to parse.
4122            If an Expression instance is passed, this is used as-is.
4123        dialect (str): the dialect used to parse the input expression (in the case that the
4124            input expression is a SQL string).
4125        **opts: other options to use to parse the input expressions (again, in the case
4126            that the input expression is a SQL string).
4127
4128    Returns:
4129        Condition: the expression
4130    """
4131    return maybe_parse(  # type: ignore
4132        expression,
4133        into=Condition,
4134        dialect=dialect,
4135        **opts,
4136    )
4137
4138
4139def and_(*expressions, dialect=None, **opts) -> And:
4140    """
4141    Combine multiple conditions with an AND logical operator.
4142
4143    Example:
4144        >>> and_("x=1", and_("y=1", "z=1")).sql()
4145        'x = 1 AND (y = 1 AND z = 1)'
4146
4147    Args:
4148        *expressions (str | Expression): the SQL code strings to parse.
4149            If an Expression instance is passed, this is used as-is.
4150        dialect (str): the dialect used to parse the input expression.
4151        **opts: other options to use to parse the input expressions.
4152
4153    Returns:
4154        And: the new condition
4155    """
4156    return _combine(expressions, And, dialect, **opts)
4157
4158
4159def or_(*expressions, dialect=None, **opts) -> Or:
4160    """
4161    Combine multiple conditions with an OR logical operator.
4162
4163    Example:
4164        >>> or_("x=1", or_("y=1", "z=1")).sql()
4165        'x = 1 OR (y = 1 OR z = 1)'
4166
4167    Args:
4168        *expressions (str | Expression): the SQL code strings to parse.
4169            If an Expression instance is passed, this is used as-is.
4170        dialect (str): the dialect used to parse the input expression.
4171        **opts: other options to use to parse the input expressions.
4172
4173    Returns:
4174        Or: the new condition
4175    """
4176    return _combine(expressions, Or, dialect, **opts)
4177
4178
4179def not_(expression, dialect=None, **opts) -> Not:
4180    """
4181    Wrap a condition with a NOT operator.
4182
4183    Example:
4184        >>> not_("this_suit='black'").sql()
4185        "NOT this_suit = 'black'"
4186
4187    Args:
4188        expression (str | Expression): the SQL code strings to parse.
4189            If an Expression instance is passed, this is used as-is.
4190        dialect (str): the dialect used to parse the input expression.
4191        **opts: other options to use to parse the input expressions.
4192
4193    Returns:
4194        Not: the new condition
4195    """
4196    this = condition(
4197        expression,
4198        dialect=dialect,
4199        **opts,
4200    )
4201    return Not(this=_wrap_operator(this))
4202
4203
4204def paren(expression) -> Paren:
4205    return Paren(this=expression)
4206
4207
4208SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4209
4210
4211@t.overload
4212def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4213    ...
4214
4215
4216@t.overload
4217def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4218    ...
4219
4220
4221def to_identifier(name, quoted=None):
4222    """Builds an identifier.
4223
4224    Args:
4225        name: The name to turn into an identifier.
4226        quoted: Whether or not force quote the identifier.
4227
4228    Returns:
4229        The identifier ast node.
4230    """
4231
4232    if name is None:
4233        return None
4234
4235    if isinstance(name, Identifier):
4236        identifier = name
4237    elif isinstance(name, str):
4238        identifier = Identifier(
4239            this=name,
4240            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4241        )
4242    else:
4243        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4244    return identifier
4245
4246
4247INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4248
4249
4250def to_interval(interval: str | Literal) -> Interval:
4251    """Builds an interval expression from a string like '1 day' or '5 months'."""
4252    if isinstance(interval, Literal):
4253        if not interval.is_string:
4254            raise ValueError("Invalid interval string.")
4255
4256        interval = interval.this
4257
4258    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4259
4260    if not interval_parts:
4261        raise ValueError("Invalid interval string.")
4262
4263    return Interval(
4264        this=Literal.string(interval_parts.group(1)),
4265        unit=Var(this=interval_parts.group(2)),
4266    )
4267
4268
4269@t.overload
4270def to_table(sql_path: str | Table, **kwargs) -> Table:
4271    ...
4272
4273
4274@t.overload
4275def to_table(sql_path: None, **kwargs) -> None:
4276    ...
4277
4278
4279def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4280    """
4281    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4282    If a table is passed in then that table is returned.
4283
4284    Args:
4285        sql_path: a `[catalog].[schema].[table]` string.
4286
4287    Returns:
4288        A table expression.
4289    """
4290    if sql_path is None or isinstance(sql_path, Table):
4291        return sql_path
4292    if not isinstance(sql_path, str):
4293        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4294
4295    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4296    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4297
4298
4299def to_column(sql_path: str | Column, **kwargs) -> Column:
4300    """
4301    Create a column from a `[table].[column]` sql path. Schema is optional.
4302
4303    If a column is passed in then that column is returned.
4304
4305    Args:
4306        sql_path: `[table].[column]` string
4307    Returns:
4308        Table: A column expression
4309    """
4310    if sql_path is None or isinstance(sql_path, Column):
4311        return sql_path
4312    if not isinstance(sql_path, str):
4313        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4314    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4315    return Column(this=column_name, table=table_name, **kwargs)
4316
4317
4318def alias_(
4319    expression: str | Expression,
4320    alias: str | Identifier,
4321    table: bool | t.Sequence[str | Identifier] = False,
4322    quoted: t.Optional[bool] = None,
4323    dialect: DialectType = None,
4324    **opts,
4325):
4326    """Create an Alias expression.
4327
4328    Example:
4329        >>> alias_('foo', 'bar').sql()
4330        'foo AS bar'
4331
4332        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4333        '(SELECT 1, 2) AS bar(a, b)'
4334
4335    Args:
4336        expression: the SQL code strings to parse.
4337            If an Expression instance is passed, this is used as-is.
4338        alias: the alias name to use. If the name has
4339            special characters it is quoted.
4340        table: Whether or not to create a table alias, can also be a list of columns.
4341        quoted: whether or not to quote the alias
4342        dialect: the dialect used to parse the input expression.
4343        **opts: other options to use to parse the input expressions.
4344
4345    Returns:
4346        Alias: the aliased expression
4347    """
4348    exp = maybe_parse(expression, dialect=dialect, **opts)
4349    alias = to_identifier(alias, quoted=quoted)
4350
4351    if table:
4352        table_alias = TableAlias(this=alias)
4353        exp.set("alias", table_alias)
4354
4355        if not isinstance(table, bool):
4356            for column in table:
4357                table_alias.append("columns", to_identifier(column, quoted=quoted))
4358
4359        return exp
4360
4361    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4362    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4363    # for the complete Window expression.
4364    #
4365    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4366
4367    if "alias" in exp.arg_types and not isinstance(exp, Window):
4368        exp = exp.copy()
4369        exp.set("alias", alias)
4370        return exp
4371    return Alias(this=exp, alias=alias)
4372
4373
4374def subquery(expression, alias=None, dialect=None, **opts):
4375    """
4376    Build a subquery expression.
4377
4378    Example:
4379        >>> subquery('select x from tbl', 'bar').select('x').sql()
4380        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4381
4382    Args:
4383        expression (str | Expression): the SQL code strings to parse.
4384            If an Expression instance is passed, this is used as-is.
4385        alias (str | Expression): the alias name to use.
4386        dialect (str): the dialect used to parse the input expression.
4387        **opts: other options to use to parse the input expressions.
4388
4389    Returns:
4390        Select: a new select with the subquery expression included
4391    """
4392
4393    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4394    return Select().from_(expression, dialect=dialect, **opts)
4395
4396
4397def column(
4398    col: str | Identifier,
4399    table: t.Optional[str | Identifier] = None,
4400    schema: t.Optional[str | Identifier] = None,
4401    quoted: t.Optional[bool] = None,
4402) -> Column:
4403    """
4404    Build a Column.
4405
4406    Args:
4407        col: column name
4408        table: table name
4409        schema: schema name
4410        quoted: whether or not to force quote each part
4411    Returns:
4412        Column: column instance
4413    """
4414    return Column(
4415        this=to_identifier(col, quoted=quoted),
4416        table=to_identifier(table, quoted=quoted),
4417        schema=to_identifier(schema, quoted=quoted),
4418    )
4419
4420
4421def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast:
4422    """Cast an expression to a data type.
4423
4424    Example:
4425        >>> cast('x + 1', 'int').sql()
4426        'CAST(x + 1 AS INT)'
4427
4428    Args:
4429        expression: The expression to cast.
4430        to: The datatype to cast to.
4431
4432    Returns:
4433        A cast node.
4434    """
4435    expression = maybe_parse(expression, **opts)
4436    return Cast(this=expression, to=DataType.build(to, **opts))
4437
4438
4439def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4440    """Build a Table.
4441
4442    Args:
4443        table (str | Expression): column name
4444        db (str | Expression): db name
4445        catalog (str | Expression): catalog name
4446
4447    Returns:
4448        Table: table instance
4449    """
4450    return Table(
4451        this=to_identifier(table, quoted=quoted),
4452        db=to_identifier(db, quoted=quoted),
4453        catalog=to_identifier(catalog, quoted=quoted),
4454        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4455    )
4456
4457
4458def values(
4459    values: t.Iterable[t.Tuple[t.Any, ...]],
4460    alias: t.Optional[str] = None,
4461    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4462) -> Values:
4463    """Build VALUES statement.
4464
4465    Example:
4466        >>> values([(1, '2')]).sql()
4467        "VALUES (1, '2')"
4468
4469    Args:
4470        values: values statements that will be converted to SQL
4471        alias: optional alias
4472        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4473         If either are provided then an alias is also required.
4474         If a dictionary is provided then the first column of the values will be casted to the expected type
4475         in order to help with type inference.
4476
4477    Returns:
4478        Values: the Values expression object
4479    """
4480    if columns and not alias:
4481        raise ValueError("Alias is required when providing columns")
4482    table_alias = (
4483        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4484        if columns
4485        else TableAlias(this=to_identifier(alias) if alias else None)
4486    )
4487    expressions = [convert(tup) for tup in values]
4488    if columns and isinstance(columns, dict):
4489        types = list(columns.values())
4490        expressions[0].set(
4491            "expressions",
4492            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4493        )
4494    return Values(
4495        expressions=expressions,
4496        alias=table_alias,
4497    )
4498
4499
4500def var(name: t.Optional[str | Expression]) -> Var:
4501    """Build a SQL variable.
4502
4503    Example:
4504        >>> repr(var('x'))
4505        '(VAR this: x)'
4506
4507        >>> repr(var(column('x', table='y')))
4508        '(VAR this: x)'
4509
4510    Args:
4511        name: The name of the var or an expression who's name will become the var.
4512
4513    Returns:
4514        The new variable node.
4515    """
4516    if not name:
4517        raise ValueError(f"Cannot convert empty name into var.")
4518
4519    if isinstance(name, Expression):
4520        name = name.name
4521    return Var(this=name)
4522
4523
4524def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4525    """Build ALTER TABLE... RENAME... expression
4526
4527    Args:
4528        old_name: The old name of the table
4529        new_name: The new name of the table
4530
4531    Returns:
4532        Alter table expression
4533    """
4534    old_table = to_table(old_name)
4535    new_table = to_table(new_name)
4536    return AlterTable(
4537        this=old_table,
4538        actions=[
4539            RenameTable(this=new_table),
4540        ],
4541    )
4542
4543
4544def convert(value) -> Expression:
4545    """Convert a python value into an expression object.
4546
4547    Raises an error if a conversion is not possible.
4548
4549    Args:
4550        value (Any): a python object
4551
4552    Returns:
4553        Expression: the equivalent expression object
4554    """
4555    if isinstance(value, Expression):
4556        return value
4557    if value is None:
4558        return NULL
4559    if isinstance(value, bool):
4560        return Boolean(this=value)
4561    if isinstance(value, str):
4562        return Literal.string(value)
4563    if isinstance(value, float) and math.isnan(value):
4564        return NULL
4565    if isinstance(value, numbers.Number):
4566        return Literal.number(value)
4567    if isinstance(value, tuple):
4568        return Tuple(expressions=[convert(v) for v in value])
4569    if isinstance(value, list):
4570        return Array(expressions=[convert(v) for v in value])
4571    if isinstance(value, dict):
4572        return Map(
4573            keys=[convert(k) for k in value],
4574            values=[convert(v) for v in value.values()],
4575        )
4576    if isinstance(value, datetime.datetime):
4577        datetime_literal = Literal.string(
4578            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4579        )
4580        return TimeStrToTime(this=datetime_literal)
4581    if isinstance(value, datetime.date):
4582        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4583        return DateStrToDate(this=date_literal)
4584    raise ValueError(f"Cannot convert {value}")
4585
4586
4587def replace_children(expression, fun):
4588    """
4589    Replace children of an expression with the result of a lambda fun(child) -> exp.
4590    """
4591    for k, v in expression.args.items():
4592        is_list_arg = isinstance(v, list)
4593
4594        child_nodes = v if is_list_arg else [v]
4595        new_child_nodes = []
4596
4597        for cn in child_nodes:
4598            if isinstance(cn, Expression):
4599                for child_node in ensure_collection(fun(cn)):
4600                    new_child_nodes.append(child_node)
4601                    child_node.parent = expression
4602                    child_node.arg_key = k
4603            else:
4604                new_child_nodes.append(cn)
4605
4606        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4607
4608
4609def column_table_names(expression):
4610    """
4611    Return all table names referenced through columns in an expression.
4612
4613    Example:
4614        >>> import sqlglot
4615        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4616        ['c', 'a']
4617
4618    Args:
4619        expression (sqlglot.Expression): expression to find table names
4620
4621    Returns:
4622        list: A list of unique names
4623    """
4624    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4625
4626
4627def table_name(table) -> str:
4628    """Get the full name of a table as a string.
4629
4630    Args:
4631        table (exp.Table | str): table expression node or string.
4632
4633    Examples:
4634        >>> from sqlglot import exp, parse_one
4635        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4636        'a.b.c'
4637
4638    Returns:
4639        The table name.
4640    """
4641
4642    table = maybe_parse(table, into=Table)
4643
4644    if not table:
4645        raise ValueError(f"Cannot parse {table}")
4646
4647    return ".".join(
4648        part
4649        for part in (
4650            table.text("catalog"),
4651            table.text("db"),
4652            table.name,
4653        )
4654        if part
4655    )
4656
4657
4658def replace_tables(expression, mapping):
4659    """Replace all tables in expression according to the mapping.
4660
4661    Args:
4662        expression (sqlglot.Expression): expression node to be transformed and replaced.
4663        mapping (Dict[str, str]): mapping of table names.
4664
4665    Examples:
4666        >>> from sqlglot import exp, parse_one
4667        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4668        'SELECT * FROM c'
4669
4670    Returns:
4671        The mapped expression.
4672    """
4673
4674    def _replace_tables(node):
4675        if isinstance(node, Table):
4676            new_name = mapping.get(table_name(node))
4677            if new_name:
4678                return to_table(
4679                    new_name,
4680                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4681                )
4682        return node
4683
4684    return expression.transform(_replace_tables)
4685
4686
4687def replace_placeholders(expression, *args, **kwargs):
4688    """Replace placeholders in an expression.
4689
4690    Args:
4691        expression (sqlglot.Expression): expression node to be transformed and replaced.
4692        args: positional names that will substitute unnamed placeholders in the given order.
4693        kwargs: keyword arguments that will substitute named placeholders.
4694
4695    Examples:
4696        >>> from sqlglot import exp, parse_one
4697        >>> replace_placeholders(
4698        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4699        ... ).sql()
4700        'SELECT * FROM foo WHERE a = b'
4701
4702    Returns:
4703        The mapped expression.
4704    """
4705
4706    def _replace_placeholders(node, args, **kwargs):
4707        if isinstance(node, Placeholder):
4708            if node.name:
4709                new_name = kwargs.get(node.name)
4710                if new_name:
4711                    return to_identifier(new_name)
4712            else:
4713                try:
4714                    return to_identifier(next(args))
4715                except StopIteration:
4716                    pass
4717        return node
4718
4719    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4720
4721
4722def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4723    """Transforms an expression by expanding all referenced sources into subqueries.
4724
4725    Examples:
4726        >>> from sqlglot import parse_one
4727        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
4728        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
4729
4730    Args:
4731        expression: The expression to expand.
4732        sources: A dictionary of name to Subqueryables.
4733        copy: Whether or not to copy the expression during transformation. Defaults to True.
4734
4735    Returns:
4736        The transformed expression.
4737    """
4738
4739    def _expand(node: Expression):
4740        if isinstance(node, Table):
4741            name = table_name(node)
4742            source = sources.get(name)
4743            if source:
4744                subquery = source.subquery(node.alias or name)
4745                subquery.comments = [f"source: {name}"]
4746                return subquery
4747        return node
4748
4749    return expression.transform(_expand, copy=copy)
4750
4751
4752def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
4753    """
4754    Returns a Func expression.
4755
4756    Examples:
4757        >>> func("abs", 5).sql()
4758        'ABS(5)'
4759
4760        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
4761        'CAST(5 AS DOUBLE)'
4762
4763    Args:
4764        name: the name of the function to build.
4765        args: the args used to instantiate the function of interest.
4766        dialect: the source dialect.
4767        kwargs: the kwargs used to instantiate the function of interest.
4768
4769    Note:
4770        The arguments `args` and `kwargs` are mutually exclusive.
4771
4772    Returns:
4773        An instance of the function of interest, or an anonymous function, if `name` doesn't
4774        correspond to an existing `sqlglot.expressions.Func` class.
4775    """
4776    if args and kwargs:
4777        raise ValueError("Can't use both args and kwargs to instantiate a function.")
4778
4779    from sqlglot.dialects.dialect import Dialect
4780
4781    args = tuple(convert(arg) for arg in args)
4782    kwargs = {key: convert(value) for key, value in kwargs.items()}
4783
4784    parser = Dialect.get_or_raise(dialect)().parser()
4785    from_args_list = parser.FUNCTIONS.get(name.upper())
4786
4787    if from_args_list:
4788        function = from_args_list(args) if args else from_args_list.__self__(**kwargs)  # type: ignore
4789    else:
4790        kwargs = kwargs or {"expressions": args}
4791        function = Anonymous(this=name, **kwargs)
4792
4793    for error_message in function.error_messages(args):
4794        raise ValueError(error_message)
4795
4796    return function
4797
4798
4799def true():
4800    """
4801    Returns a true Boolean expression.
4802    """
4803    return Boolean(this=True)
4804
4805
4806def false():
4807    """
4808    Returns a false Boolean expression.
4809    """
4810    return Boolean(this=False)
4811
4812
4813def null():
4814    """
4815    Returns a Null expression.
4816    """
4817    return Null()
4818
4819
4820# TODO: deprecate this
4821TRUE = Boolean(this=True)
4822FALSE = Boolean(this=False)
4823NULL = Null()